deepa lekshmi (10/16/2012)
Hi All,We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps:
1. create a table and insert data into that using 'SELECT INTO' statement, which has many joins.
2. Update the table - this also has many joins.
3. Create indexes on the created tables.
What approach should we use here? Since there is no ETL in the SP, we converted each set of statements to one ‘Execute SQL’ task. Is this the right approach?
What we can do to improve the performance of the packages?
There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP). Which SSIS task would be suitable here? I tried using ‘ExecuteSQL task’ with ‘RetainSameConnection’ true.
Why are you required to convert the stored procedures to SSIS? If you have a process that is working without problems, I wouldn't monkey with it. Or are there issues that you're trying to correct?
You can easily move your logic into the 'Execute SQL' task, but you're not going to see any real performance difference.
Good luck,
Rob