Hi, Sorry reposting the same question, but nobody replied ...? 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 Dmitry_SQL, What is the error you are getting? Perhaps you misconfigured the mapping. E.g. enumeration starts with the 0 (zero). Arthur My Blog

See this for help How to: Map Query Parameters to Variables in an Execute SQL Task http://technet.microsoft.com/en-us/library/ms140355.aspxArthur My Blog

If you want to pass the result from an Execute SQL Statement to an SSIS variable, you need to create a resultset which you can bind to the variable. For example: And on the Result Set tab you can map that column from the resultset to the variable: So give your case-statement an alias and use that in the Result Set tab. Something like: declare @ACCT_YYYYMM varchar (6) set @Acct_YYYYMM = '201105' select 'myColumn'= case when @Acct_YYYYMM = '201106' then '2' else '1' end Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I think I miss some fundamental understanding here. I created a variable User::YYYYMM Scope = Package Type = String Value = 201106 Also created a variable User::SW Scope = Package Type = String Value = Opened "Execute SQL Task" editor. SQL statement: ----------------------------------------------------------- declare @YYYYMM varcahar (6) declare @SW varchar (1) set @YYYYMM = ? set @SW = case when @YYYYMM = '201106' then 'Y' else 'N' end ----------------------------------------------------------------------------- Error: [Execute SQL Task] Error: Executing the query "declare @YYYYMM varcahar (6) declare @SW ..." failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Thanks, Dmitry

Do it as SSIS Expression, why do you need SQL for this?Arthur My Blog

People, Thank you for your help. I finally resolved this issue today. I did not understand how @YYYYMM variable inside of my SQL statement receives value from User::YYYYMM which was set to 201106. But now I connected the dots and the key is the Parameter mapping and the Result Set mapping. When it is done correctly it all make sense now. What a releive......!!!!!!!!! Dmitry.

Hi Dmitry_SQL, What is the error you are getting? Perhaps you misconfigured the mapping. E.g. enumeration starts with the 0 (zero). Arthur My Blog I told you in the first reply Dmitry. Arthur My Blog