In SSIS 2005 I'm trying to use an "Execute SQL Task" using the SQL Statement "IN()" which is fed a parameter from a variable which is a string comma delimited list i.e. jan, feb, mar. However this is not working for me. Could someone please give me the correct SQL Statement syntax, and if there are any special metadata configuration properties that I should configure please let me know. My current SQL statement reads as follows: Select * from CMS_Import WHERE FirstVDN IN(?) I have set the parameter comma delimited string list on the Parameter Mapping Tab of the Metadata configuration window. I am relatively new to SSIS and would really appreciate any help you can offer.

My $0.02: Never use Select * (all) you have to specify the fields list explicitly. And perhaps a better approach is to set the whole SQL where you set that list variable up, you may use SQL from variable option, this way no need to construct the expression in the Execute SQL task. A good place to have that all + being able to test the SQL statement is the Script Task: I think this post explains it well: http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspxArthur My Blog

What is the error? If you pass the parameter as: 'Jan,Feb,Mar' then the query would be: Select * from table where VDN IN ('Jan,Feb,Mar'). But you need following query: Select * from table where VDN IN ('Jan','Feb','Mar'). You should create a new variable to use as parameter by replacing coma (,) with ',' in the parameter string using expression as: "'"+REPLACE(@[User::param],",","','")+"'" Nitesh Rai- Please mark the post as answered if it answers your question

Thanks for the replies so far, I think I now have my variable string and my SQL Statement correctly formatted. However I have noticed that after adding my variable string as a parameter to the Execute SQL Task component, the package fails on execution. It gives the following message: failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any ideas?

In the parameter mapping property page of the Execute SQL task specify the variable of type Int32 and direction output, the parameter name should include your variable if this does not help provide us with: 1) The query; and 2) The images of how you set all up may wan to see http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters for helpArthur My Blog

Thanks. I've actually changed the parameter mapping data type to VARCHAR so that it corresponds with the fact that its a string variable, and the Parameter Name is set to zero, and I'm now getting the following error message: failed with the following error: "Invalid column name ' + @? + '.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

That error makes it sound like you've typed the expression directly into the property, and not used a property expression. Confusing, I know. Does that give you enough information, or do you need more help? Talk to me now on

Todd, I don't quite understand you. I don't even think I've used a property expression. Should I be? Can you elaborate?

Mood_User: I asked to provide an image in #2 above, I guess it is time now to. Please.Arthur My Blog

Hi MooD_User, Please refer to the article about mapping parameters in Execute SQL Task: http://www.rafael-salas.com/2007/11/ssis-mapping-parameter-inside-of.html For more information about Execute SQL Task, please see: http://www.sqlis.com/post/The-Execute-SQL-Task.aspx Thanks, Eileen

You can create a table valued function and use that as a table, for further detail check this linek Comma separated string as a sql parameter