June 26, 2007 at 11:46 pm
HI all,
Can any one tell me how to call stored procedure from SSIS. the stored procedure is in sql server database .
step 1: Extract data from Excel
Step 2: Transform process
Step 3: Destination (Loading in to sql table)
Here i want to load data to sql table.i have one stored procedure it handle If the the data already exists it should update otherwise insert.
Hoe to can i call that stored procedure while loading data into DB.
June 27, 2007 at 12:15 am
You should not use a proc to insert data because it will be slow. The whole idea of SSIS pipeline is to do all your transformation en-route and then insert the cleansed data by using bulk load. It will be a lot faster.
To work out which are new rows and old rows, use a lookup component. Anything not matching, redirect to the error output. These you will then insert using the OLEDB destination.
Any rows matching, i.e. rows already in the table, need to be updated. For this you can either use a OLEDB component or use a bulk load into a temp table and then use a SQL task to do the update afterwards. The second option is preferable if you have a lot of rows as single row updates are really slow.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply