Hi, I got really stuck here. 1. Created variable User::sw int16 value 0. 2. Created a "Execute SQL Task". OLE DB connection. Result set None. 3. Created SQL statement: declare @sw int set @sw = 2 4. Executed successfully but the value of @sw remained 0. What is wrong....? Thanks, Dmitry.

You cannot set the value of an SSIS Package Variable with an Execute SQL statement. Either set it at Design Time, set it from a Configuration, or use a Script Task and reference the Variable as ReadWrite and supply code like this: Dts.Variables("sw").Value = 2Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

If you want values generated inside an Execute SQL Task to be pushed into an SSIS variable, that won't work. SSIS variables are separate from variables that you declare inside a SQL statement. Just because you've named them the same, it doesn't mean they "know" about each other. There are several ways the Execute SQL Task can "communicate" with SSIS variables. One is through parameters - you can pass values in and out. Another is by having the SQL statement return a result set. The last is by using variables and expressions to build the SQL statement itself. Talk to me now on

Todd, Maybe I did not post my question correctly. Last week I created the following Execute SQL statement and it populated [User::mm] and [User::yyyy] correctly and I was able to use these values in my package. declare @temp_yyyy varchar (40), @temp_mm varchar (40) select @temp_yyyy = getdate(), @temp_mm = convert (varchar(40), getdate(), 1) select [User::mm] = substring (@temp_mm, 1, 2), [User::yyyy] = substring (@temp_yyyy, 8, 4) Thank you, Dmitry

I got it now. Though I declared @sw, my task has nothing to do with it and created a temp variable [User::sw] that is used throughout the process. I am afraid to admit how many hours I spent yesterday trying to make it work. Thanks....!!!!!!

Hi, Can I ask another related question...? I created the following Execute SQL statement and it works perfectly: ------------------------------------------------------------------------ declare @ACCT_YYYYMM varchar (6) set @Acct_YYYYMM = '201105' select [User::Quarter_SW] = case when @Acct_YYYYMM = '201106' then '2' else '1' end -------------------------------------------------------- But I can not figure out how to pass it an external date parm. I created var ACCT_YYYYMM and gave it a value '201108'. But Execute SQL statement does not allow me to - set @Acct_YYYYMM = ? Thanks, Dmitry

Hi, Can I ask another related question...? I created the following Execute SQL statement and it works perfectly: ------------------------------------------------------------------------ declare @ACCT_YYYYMM varchar (6) set @Acct_YYYYMM = '201105' select [User::Quarter_SW] = case when @Acct_YYYYMM = '201106' then '2' else '1' end -------------------------------------------------------- But I can not figure out how to pass it an external date parm. I created var ACCT_YYYYMM and gave it a value '201108'. But Execute SQL statement does not allow me to - set @Acct_YYYYMM = ? Thanks, Dmitry http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/be1a33f9-9766-458b-bd11-bea151419c67Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com