June 10, 2009 at 10:35 am
Hi,
I'm new with SSIS and I need to loop through a table and use it as an input to my stored procedure.
Example:
Table A
col1 col2 col3
val1 val2 Active
val4 val5 Inactive
where col3 = 'Active' (maybe a view will serve this condition)
Stored Procedure
SP_xyz(col1,col2,col3)
A step-by-step example will surely be a great help.
Thanks,
Christopher
June 10, 2009 at 10:51 am
Step 1: Execute SQL Step - SELECT Col1, Col2, Col3 FROM YourTable WHERE........
-- Result Set = Full Result Set
-- Result Set tab - add result set Result Name = 0, Variable Name = (you'll need to create a variable of type Object)
Step 2: For Each Loop container
-- Collection Enumerator = Foreach ADO Enumerator
-- ADO object source variable = Your variable from above
-- Enumeration Mode = Rows in th efirst table
-- Variable Mappings = here you'll create 3 mappings, one for each of your columns in your recordset with index 0,1,2. This gets the values from your query into package level varaibles
Step 3: Execute SQL Step inside for each container
-- Result Set = None
-- SQL Statement = EXEC yourSPname @parm1=?, @parm2=?, @parm3=? (use your input parameter names here)
-- Parameer mapping = here you'll add 3 parameters, one for each input parameter in your SP.
-- Variable Name = your variables from Step 2.
-- Direction = Input
-- Data Type = Data types from Sp parameters
-- ParameterName = parameter names from SP parameters (and from your EXEC statement above)
This should get you going, post any errors or further questions to this thread.
June 10, 2009 at 10:59 am
Thanks. That was a quick one.
I'll try this out.
June 10, 2009 at 11:32 am
I'm getting this error:
[Execute SQL Task] Error: Executing the query "exec AutoVer_Staging_DelqLoan @Trans_date = ?,@StoreNo = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm not sure about the part:
exec AutoVer_Staging_DelqLoan @Trans_date = ?,@StoreNo = ? ( I have reduced it to 2 params for testing)
I added the package variables: v_Trans_Date and v_Store_no and set the parameter names Trans_Date and Store_No correspondingly in the Parameter Mapping of Execute SQL Task
June 10, 2009 at 11:45 am
Ooppss... I forgot to declare the other two variables.
It is working now.
Thank you so so much!!!
June 10, 2009 at 11:57 am
John Rowan (6/10/2009)
Step 1: Execute SQL Step - SELECT Col1, Col2, Col3 FROM YourTable WHERE........-- Result Set = Full Result Set
-- Result Set tab - add result set Result Name = 0, Variable Name = (you'll need to create a variable of type Object)
Step 2: For Each Loop container
-- Collection Enumerator = Foreach ADO Enumerator
-- ADO object source variable = Your variable from above
-- Enumeration Mode = Rows in th efirst table
-- Variable Mappings = here you'll create 3 mappings, one for each of your columns in your recordset with index 0,1,2. This gets the values from your query into package level varaibles
Step 3: Execute SQL Step inside for each container
-- Result Set = None
-- SQL Statement = EXEC yourSPname @parm1=?, @parm2=?, @parm3=? (use your input parameter names here)
-- Parameer mapping = here you'll add 3 parameters, one for each input parameter in your SP.
-- Variable Name = your variables from Step 2.
-- Direction = Input
-- Data Type = Data types from Sp parameters
-- ParameterName = parameter names from SP parameters (and from your EXEC statement above)
This should get you going, post any errors or further questions to this thread.
Geez, we can see that is not your first SSIS package... Great job John... I will certainly keep this for further reference! http://www.sqlservercentral.com/Forums/Skins%5CClassic%5CImages/MessageIcons/Smile.gif
Cheers,
J-F
June 10, 2009 at 12:48 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
