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.
Thanks in advance
The source & destination databases are on same server or on different servers?
1. If both source and destination databases are on different servers & therefore you are using link server to connect, you may get some performance improvement using SSIS.
2. However, if both databases are on same server you are not going to get any performance improvment when using SSIS.
3. Talking about maintainability, stored procedures are very much easy to manage compared to SSIS packages.