1

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.

dmarkez
  • 73
  • 1
  • 2
  • 11

1 Answers1

4

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.

Jeroen Bolle
  • 1,836
  • 1
  • 12
  • 15
  • Thanks Jeroen! Yes, I figured it out after I've posted and now I'm stack on a different error. The SSIS package didnt recognized the input parameter. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Procedure or function '' expects parameter '@XmlID', which was not supplied.". – dmarkez Jul 18 '12 at 20:54
  • When does this error occur? Are you sure the @XMLID parameter holds a value? – Jeroen Bolle Jul 18 '12 at 23:54
  • The issue is now resolved. The stored procedure variable must be the same as the parameter name at the SSIS(instead of Parameter0...). I have also an invalid SSIS package location :D. – dmarkez Jul 19 '12 at 02:00