Execute SQL Task??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Execute SQL Task???

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2007-03-17 : 04:47:58

hi

I have created an Execute SQL Task package and save as package.dtsx

using northwind database

In this package, i have a sql statement: Select * into tblTest from dbo.Orders where OrderID >= ? and OrderID <= ?

I have created 2 package level variables which is OrderID1 and OrderID2, both are Int32 and Value = 0.

I have also used parameter mapping: user:OrderID1,Input, Long, 0, -1

user:OrderID2,Input, Long, 0, -1

I have created a proc to test:

Create Proc spTest
@Value1 int,
@Value2 int
as
Declare @cmd varchar(1000)
set @cmd = 'dtexec /FILE "C:\Package.dtsx" /SET \Package.Variables[OrderID1].Value;@Value1 /SET \Package.Variables[OrderID2].Value;@Value2'
exec master..xp_cmdshell @cmd

When I execute spTest 10249,11029, it gave me "it could set \Package.variables[OrderID1].value;@Value1 and so on.

I hope someone could help me with this. Thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-03-19 : 05:35:46

You need to add the add the initialised value of your variables to your cmd string (as opposed to their names). This means you'll have to cast them to VARCHAR:

set @cmd = 'dtexec /FILE "C:\Package.dtsx" /SET \Package.Variables[OrderID1].Value;' + CAST(VARCHAR(10), @Value1) + ' /SET \Package.Variables[OrderID2].Value;' + CAST(VARCHAR(10), @Value2)
exec master..xp_cmdshell @cmd

Additionally, you might want to have a look at this link:[url]http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx[/url]

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources