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
Execute SQL task :
As you can see I changed the Result Set to Full Result Set. On the Result Set page
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
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)
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
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
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
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
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
Execute SQL task :
As you can see I changed the Result Set to Full Result Set. On the Result Set page
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
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)
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
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
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
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