July 16, 2009 at 8:59 am
I have a simple package with 1 dataflow. The dataflow uses an OLEDB source which calls a stored procedure that takes 2 parameters, a start date and an end date. This OLEDB source then outputs to a flat file destination. I have created the 2 variables and when I set them to values withing the package and execute the package everything works fine.
What I need to do though is pass these two variable values in via my DTEXEC command. Whenever I do this it does not seem to record the new values and set them to the variables. Here is how I am calling it, any help would be greatly appreciated:
DECLARE @cmd VARCHAR(1000)
SET @cmd = 'DTEXEC /sq PASS_PARAM /ser SRVMGRSQL
/SET \Package.Variables[startdate].Value;"06-23-2009"
/SET \Package.Variables[enddate].Value;"06-28-2009";'
EXECUTE xp_cmdshell @cmd
July 16, 2009 at 10:24 pm
What is the scope of the variables, it looks like you are referencing them at the package level, if they are defined in the dataflow their scope will be different like:
DECLARE @cmd VARCHAR(1000)
SET @cmd = 'DTEXEC /sq PASS_PARAM /ser SRVMGRSQL
/SET \Package.DataFlowName.Variables[startdate].Value;"06-23-2009"
/SET \Package.DataFlowName.Variables[enddate].Value;"06-28-2009";'
EXECUTE xp_cmdshell @cmd
I'm not sure that is 100% correct but its close.. I think you have a scope problem..
CEWII
July 17, 2009 at 5:21 am
That looks like what it was I didn't even notice that I had cerated the variables outside of the package scope. Thanks.
July 19, 2009 at 9:15 pm
You are very welcome..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply