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)  Running a stored procedure with parameters.

Author  Topic  ertemen Starting Member 6 Posts Posted - 2010-06-04 : 11:06:48 Hi All, I have a particular problem while creating a SSIS package. I will try to explain what’s happening with screen shots. What I need to do is to create package that runs a SQL query to get the siteids and chuck them in to a variable. And then run a loop trough the variable and run a parameterized stored procedure and store the results in a flat file. For that I have created ParamDay, ParamMonth, Paramyear parameters (integers) which will be used for running the stored procedure, rsSites ( an object variable to store the execute SQL task results) and ParamSiteID ( another integer data type variable to be used looping through rsSites result set) PS: I am sorry about the quality of the screen shots ( ALT + print screen doesn’t seem to work correctly on RDP) Project Overview – so far http://img694.imageshack.us/f/29995385.jpg/ Execute SQL task : http://img85.imageshack.us/f/12774875.jpg/ As you can see I changed the Result Set to Full Result Set. On the Result Set page http://img29.imageshack.us/f/86587232.jpg/ I have changed ResultName = 0 ( string value causes an error) and selected the variable name to rsSites. So what it means is to run the SQL and store the resultset in to a object typed variable called rsSites. For each Loop Container http://img96.imageshack.us/i/53157910.jpg/ I have selected Foreach ADO enumerator and selected ADO object source variable to be my object variable rsSites. On the variable Mapping page I have selected other Site variable ( ParamSiteID) http://img442.imageshack.us/f/61796696.jpg/ So that while looping each siteid from rsSites is passed this variable to be used while running the stored procedure. Data Flow Task ( where the problem is) On oledb data source http://img338.imageshack.us/i/17391229.jpg/ I selected my oledb connection and selected SQL command to be my data access mode and then I put below exec [dbo].[dll_getMapleCashFileData] @regionOrSite = ?,@myday = ? , @mymonth = ? , @myyear = ? I know for a fact that SSIS case sensitive so parameter names are exactly same as in procedure definition. As you can see there are 4 parameters http://img243.imageshack.us/i/35570740.jpg/ In the parameter window I matched procedure parameters to ssis variables . After all of that when I attempt to preview so that I can map the return dataset to flat file destionation dataset which I am using a extisting file for, I get the following error http://img411.imageshack.us/i/23756193.jpg/ Without that available columns are empty in dataflow task and can’t map the flat file destination so all package fails. If change the ? to values it works fine but it is useless if I can’t pass values from variables. So far I tried • To set the delay validation option to True at package level. • To change the combination of running the stored procedure. ( put ? only didn’t make any difference) • To set values instead of ? first do all the mappings and then change it to ? again and save the package. But as soon as I click on OK button on the window if loses all the columns. Has anyone had the same issue? How are supposed fix this? Any idea? Enis