Passing parameters to the stored proc from csv source to populate data in the destinaton
Hi all, The task I have iS, I have a source is the CSV which needs to be populate populated in the SQL SERVER destination. I have written a stored proc which needs around 7 parameters. This stroed procedure takes the parameters, and populates the data in 5 different tables in the target. These parameters are supposed to come from a CSV as a source. I want to do this Using SSIS and i am not sure how map the CSV Columns as the parameters to the stored procedure in SSIS I am planing to design my package this way - Control flow task : Dataflow task, execute sql task - Dataflow task : flat file source, and SQL SERVER destination. Am i in the right direction, if so, I want to know what kind of properties and the mappings , are to be done at the SSIS level to get this task done. here are the parameters in the procedure. @customerid varchar(50), @Name varchar(200), @abbrev varchar(50), @status bit, @startdate datetime, @enddate datetime, @prodName varchar(100), @prodNumber varchar(100), @website varchar(100) Sample data Exec registration (11,'abc company', 'abc', 'active','1/12/2004', '1/14/2010', ''01-2343-AS ','2343'
www.abccompany.com') Any help is immensely appreciated.
Hi Katie, you are on the right track, you would have a dataflow task. And in that dataflow you would have a Flat file Source and a OLEDB Command. Create your flat file source and setup the connect etc, then after the flat file drag a OLEDB command transformation onto the data flow. One the CONNECTION MANAGERS tab set the appropriate connection manager, then on the COMPONENT PROPERTIES tab enter the SQL command below The question marks represent the parameters for the SP. EXEC storedProceName ?,?,?,? Then in the COLUMN MAPPING tab, you should see a box of available columns from your CSV source, and a available destination columns box, which are the parameters of the stored proc. Then all you need to do is map the appropriate source to the destination as below, Good luck at let us know if you need further help! ![alt text] : /upfiles/params.JPG