Pass a variable to a dts Package. -- sql server dts

tomtiv NO[at]SPAM rogers.com
1/14/2004 6:34:36 AM

Hey All,

I have seen this question a lot but i still don't understan how i do
it.

I have a sp that needs to call a dts package and pass it a variable.
So far i have my sp below that reads as follows.

DECLARE camp_cur CURSOR
FOR
SELECT campaignID FROM CampaignCodes
FOR READ ONLY
OPEN camp_cur
DECLARE @camp_num nvarchar(15) --WHILE @Camp_num <> 0
FETCH NEXT From camp_cur into @camp_num
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @camp_num
EXECUTE xp_cmdshell 'DTSRun /S "(local)" /N "600 Export theProgram To
Fixed Length Text for Mailshop" /G
"{29E33A7B-6D1A-4D62-9A98-329296A35B5A}" /W "0" /E /A' @camp_num,
NO_OUTPUT
FETCH NEXT From camp_cur into @camp_num
END
CLOSE camp_cur
DEALLOCATE camp_cur

Then i have my dts package Transformation.

DECLARE @camp_num nvarchar(15)
SELECT SRCcode, SegmentID, CampaignID, Wave, PersonUID, FirstName,
LastName, PrefLanguage, Title, AddressName, StreetAddress,
AddressLine2, City, ProvinceAbbrev, PostalCode, Country, EmailAddress,
HomePhone, Status
FROM dbo.theProgram
WHERE (PrimaryIndicator IN ('P', 'Y')) AND CampaignID = @camp_num

Can someone tell me what i am doing wrong? Or a better way to do it?

Thanks

Darren Green
1/14/2004 2:43:58 PM

Open your package in the designer.
First create a global variable in your package.

When calling the package via DTSRUN you can use the /A parameter, e.g.

/A "DataSource":"8"="XXX"

DataSource is my global variable name.
8 is type code for a String
XXX is the value.

For documentation on using /A and DTSRUN lookup up DTSRUN in SQL Server
Books Online. An easy way of generating valid DTSRUN command lines is to use
DTSRUNUI. Select the package and then click Advanced. Use the Generate
button to view the command line.

Inside your package you can use a global variable in your SQL through the
parameter support. ? is used as the place holder, e.g.

SELECT SRCcode, SegmentID, CampaignID, Wave, PersonUID, FirstName,
LastName, PrefLanguage, Title, AddressName, StreetAddress,
AddressLine2, City, ProvinceAbbrev, PostalCode, Country, EmailAddress,
HomePhone, Status
FROM dbo.theProgram
WHERE (PrimaryIndicator IN ('P', 'Y')) AND CampaignID = ?

Click the parameters button to map your global variable to the place holder.

--
Darren Green
http://www.sqldts.com

[quoted text, click to view]

sql server dts : Pass a variable to a dts Package.