SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Stored Procedures to SSIS packages


Converting Stored Procedures to SSIS packages

Author
Message
Deepa Lakshmi
Deepa Lakshmi
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 139
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
robert.gerald.taylor
robert.gerald.taylor
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1793 Visits: 1488
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
Deepa Lakshmi
Deepa Lakshmi
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 139
Rob, We are asked to do that since the long procedures are not easy to manage. Also, wanted to improve the performance. I am not sure whether converting it to packages will improve the performance, as there are no data load or any complex transformations.
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 10359
deepa lekshmi (10/17/2012)
Rob, We are asked to do that since the long procedures are not easy to manage. Also, wanted to improve the performance. I am not sure whether converting it to packages will improve the performance, as there are no data load or any complex transformations.


Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 10359
deepa lekshmi (10/16/2012)

There are SPs that call another SP many times with different parameters(The parameters are hardcoded in the SP).


There may be some scope for improvement here. That's logically equivalent to a WHILE loop.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Andy Hyslop
Andy Hyslop
SSC Eights!
SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)SSC Eights! (979 reputation)

Group: General Forum Members
Points: 979 Visits: 3037
Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.


Totally agree with Chris on this after having to do the reverse, converting SSIS to Sprocs and the performance was much improved and an easier solution to maintain..

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 10359
Andy Hyslop (10/17/2012)
Complex SSIS packages are more likely to increase maintenance cost than decrease it, because there are far more places to look for functionality, and more scope for introducing errors. A stored procedure has almost all of its' functionality in front of you. Whoever suggested this should be done to improve performance should be politely requested to explain why, as it isn't at all straightforward. I'll stick my neck out and suggest that unless you are importing from heterogenous data sources, or writing to them, an SSIS package is unlikely to perform faster than a stored procedure at the same task. And that's without mentioning bcp.


Totally agree with Chris on this after having to do the reverse, converting SSIS to Sprocs and the performance was much improved and an easier solution to maintain..


Thanks for the +1 Andy.
I'm hearing of more and more companies doing the same - "reverse engineering" their SSIS packages as stored procedures for performance and maintainability.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2283 Visits: 2816
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.


Sujeet Singh
Deepa Lakshmi
Deepa Lakshmi
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 139
Thanks all for the quick response.. We will rethink about converting SPs to packages if it can't improve the performance.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search