Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Stored Procedures to SSIS packages Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 02, 2013 11:45 AM
Points: 105, Visits: 118
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
Post #1373150
Posted Tuesday, October 16, 2012 2:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 1,135, Visits: 1,150
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
Post #1373526
Posted Wednesday, October 17, 2012 2:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 02, 2013 11:45 AM
Points: 105, Visits: 118
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.
Post #1373680
Posted Wednesday, October 17, 2012 2:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,060, Visits: 5,776
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
Post #1373683
Posted Wednesday, October 17, 2012 2:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,060, Visits: 5,776
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
Post #1373684
Posted Wednesday, October 17, 2012 3:21 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 673, Visits: 2,635
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
Post #1373699
Posted Wednesday, October 17, 2012 3:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,060, Visits: 5,776
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
Post #1373700
Posted Wednesday, October 17, 2012 4:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:34 AM
Points: 1,266, Visits: 2,320
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
Post #1373713
Posted Thursday, October 18, 2012 1:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 02, 2013 11:45 AM
Points: 105, Visits: 118
Thanks all for the quick response.. We will rethink about converting SPs to packages if it can't improve the performance.
Post #1374166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse