CODEWITHSUNDEEP
ssisdtexec
dmarkez
dmarkez

Reputation: 73

calling SSIS package with input parameters on stored procedure

I have SSIS packages that is expecting an input parameter ID(integer). I need to execute this SSIS package using stored procedure so that I can pass the value of the ID. 

DECLARE @Command varchar(1000)
    , @PackageLocation varchar(1000)
    , @PackageName varchar(1000)
    , @XmlID int

SELECT TOP 1 @XmlID = XmlId 
FROM dbo.ENROLMatchingXML
WHERE IsProcessed = 0

SET @PackageLocation = 'E:\SSIS\Package'
SET @PackageName = 'Match Names.dtsx'
SET @Command = 'DTEXEC'
    + ' /File "' + @PackageLocation + '\' + @PackageName + '"'

    + ' Set \Package.Variables[user::XMLID].Properties[Value];'  + @XmlID 
-- print just to show the string @command
print @Command

EXEC xp_cmdshell @Command

Do I need to cast the @XmlID to character? If I did, I'm getting an error - Option "Set" is not valid.

Upvotes: 1

Views: 16674

Answers (1)

Jeroen Bolle
Jeroen Bolle

Reputation: 1836

The correct syntax to execute dtexec with a parameter is:

dtexec /f "PathToMyPackage\Package.dtsx" /set \package.variables[myvariable].Value;myvalue

It seems you forgot a slash when you specified the set command option.

Upvotes: 4

Related Questions