Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 1399
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
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
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 3023
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9736
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 2801
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