Pass list to DTS Package

Question

Pass list to DTS Package

Vandy

SSCommitted

Points: 1827
More actions
November 13, 2006 at 3:09 am

#68547

Hi All
I have one table which tell me about outstanding orders of vendors. I want to mail every vendor the list of back orders. At present i designed DTS pacakage which gives back orders only for one vendor and mail him list, But i have arroud 200 vendors so how can pass full list to DTS package. I am working on SQL Server 2000 and with simple query i am creating a excel file which i am mailing to vendor.
Select * from Backorders
where Vendor='XYZ'
Kind Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sudeep Kumar SSC Journeyman Points: 86 More actions · Answer 1

If you do only select * from Backorders, will that suffice. Or you have any other criteria by which you can filter only those vendors to which you can mail the excel sheet.

Vandy SSCommitted Points: 1827 More actions · Answer 2

Actually my table has only backorder because during extraction i applied all conditions. So from my table i need to select only vendor.

Thanks

Matt Simmons SSCrazy Points: 2846 More actions · Answer 3

Sounds like the perfect place for a for each loop or a cursor. (personally I hate the cursors, but this is one place it fits rather well)

As long as you bring the email address for each company into your table you can dynamically build each piece you'll need.

Declare your cursor and 2 variables. Set it equal to Select Distinct CompanyName, Email from table.

Build your spreadsheet using your CurrentCompanyName as the where variable.

Email it to CurrentEmail.

Next or Loop the cursor.

Regards,
Matt

Jeff Oliphant SSC Enthusiast Points: 103 More actions · Answer 4

Vandy,

I am assuming you want to keep your DTS package as is -

You can do this by creating a wrapper package and calling it as a child package. There are several ways to accomplish this easily.

Basically, have the wrapper package (via SQL task) set up the tables as needed

by looping through your list and calling a child package. Just set Global variables in your child package, and set them prior to the call.

Also, you can use a table as a queue, and just call the package via SQL task.

ie.

Loop through your cursor of VendorIDs and Insert your current VendorID into a table Vendorqueue for example

Then do a dtsrun cmomand and call the package which is modified to read the currentVendorID table to get the current ID being run:

EXEC master..xp_Cmdshell 'dtsrun /S ServerName /Uuserid /Ppackagename'

Vandy SSCommitted Points: 1827 More actions · Answer 5

Hi Guys

Thanks for reply

I tried this with cursor but will going to try with wrapper package also

Thanks a lot