We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps: create a table and insert data into that using 'SELECT INTO' statement, which has many joins.Update the table - this also has many joins.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 hard-coded in the SP). Which SSIS task would be suitable here? I tried using ExecuteSQL task with RetainSameConnection true.

Hi, yes,you can add sequentially Execute SQL Task for each below statements with precedence constraint as On Success. create a table and insert data into that using 'SELECT INTO' statement, which has many joins.Update the table - this also has many joins.Create indexes on the created tables. Thanks, Sathya sathyas

If you want to take advantages of SSIS then use SSIS components. You can make use lookup for joins, derived columns,data conversion, conditional split etc to accomplish the logic. I always decide on the basis of complexity decide whether to go for SSIS or stored procedure. You can perform all the above mentioned your requirement in execute SQL task.If your requirement is just to run the same procedure in SSIS use execute SQL task.

We require to convert a list of SPs to SSIS packages. Most of the SPs do the below steps: create a table and insert data into that using 'SELECT INTO' statement, which has many joins.Update the table - this also has many joins.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 hard-coded in the SP). Which SSIS task would be suitable here? I tried using ExecuteSQL task with RetainSameConnection true. HI Deepa It looks to me as complete data trasnsfer is done via stored procedures in your old solution If you want to migrate the process in SSIS it'll be a big task and architectural change [all together depending on logic of your stored procedures... ] calling sps via execute sql tasks is simply organising your approach there isnt much you are using SSIS here but then thats the best you can do to have your project and etl process organised. Regarding performance of the package : since all logic resides in stored procedures u'd need to tune your stored procedures only there isnt much ssis can help you with here ...Hope that helps ... Kunal

Hi, I don't see the advantage of that conversion. Creating an SSIS package to call the same sentences you run on your stored procedures gives no benefit at all, only more stuff to maintain.Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Hi Deepa lakshmi, If there are very small transformation works need to do, and do not have cursors in your SPs, you can use Execute SQL Task to execute those SPs, for more information about SSIS package vs Stored Procedures, please refer to: http://dwbi1.wordpress.com/2011/02/27/ssis-work-flow-vs-stored-procedures/ Thanks, EileenPlease remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Hi Deepa lakshmi, If there are very small transformation works need to do, and do not have cursors in your SPs, you can use Execute SQL Task to execute those SPs, for more information about SSIS package vs Stored Procedures, please refer to: http://dwbi1.wordpress.com/2011/02/27/ssis-work-flow-vs-stored-procedures/ Thanks, EileenPlease remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

If not much business logic involved then I believe you will not get much advantages by converting to SSIS (only you are going to use execute SQL, join, derived column not the other components of SSIS). More over what it the main intention of thinking to convert to SSIS. Is that your database is other than SQL server? (Your SPs are in non SQL server DB?)

Deepa, the way you described seems to be the best, because SQL task will send your t-sql code to the server and be executed there, this means it will be complied each time you execute the SQL task. The SP on the other hand, as you must know, is precomplied. The question is why don't you just call your SP's from SSIS?. I guess there is more reason which you didn't mention