February 9, 2016 at 11:00 pm
Hello everyone,
I’m working on one of the existing Extract package.
Our customer would like to move the complete extract logic, implemented in this package to SQL stored procedure(s) as they are highly proficient in T-SQL compare the SSIS skills.
This package uses more than one Execute SQL tasks, Data Flow tasks, Pivot transformations, and Merge Join transformations.
This package extracts more than one file as result.
My concern is, is it really a good idea to move the extract logic from the Package to SQL store procedure(s)?
Will the stored procedure(s) perform well compare the current package?
Are there any performance wise differences between Pivot transformation and SQL Pivot T-SQL statement? Similarly, Merge Join transformation vs. T-SQL join statements.
Is it recommended to do such things while you already have many other SSIS package for Extract and Import work?
Please advise.
--
Thanks and Regards,
Ankit Shah
February 10, 2016 at 6:46 am
ankithimmatlalshah (2/9/2016)
Hello everyone,I’m working on one of the existing Extract package.
Our customer would like to move the complete extract logic, implemented in this package to SQL stored procedure(s) as they are highly proficient in T-SQL compare the SSIS skills.
This package uses more than one Execute SQL tasks, Data Flow tasks, Pivot transformations, and Merge Join transformations.
This package extracts more than one file as result.
My concern is, is it really a good idea to move the extract logic from the Package to SQL store procedure(s)?
Will the stored procedure(s) perform well compare the current package?
Are there any performance wise differences between Pivot transformation and SQL Pivot T-SQL statement? Similarly, Merge Join transformation vs. T-SQL join statements.
Is it recommended to do such things while you already have many other SSIS package for Extract and Import work?
Please advise.
Thanks and Regards,
Ankit Shah
Some very broad questions here.
My concern is, is it really a good idea to move the extract logic from the Package to SQL store procedure(s)?
If only one database is involved, technically it should be fine. Whether that is a 'good idea' is quite subjective.
Will the stored procedure(s) perform well compare the current package?
If written well, it could even outperform it.
Are there any performance wise differences between Pivot transformation and SQL Pivot T-SQL statement? Similarly, Merge Join transformation vs. T-SQL join statements.
I would expect all of these to be as fast, or considerably faster, in T-SQL.
Is it recommended to do such things while you already have many other SSIS package for Extract and Import work?
It makes things inconsistent, which is not recommended.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 10, 2016 at 10:38 pm
Hello Phil,
Thank you for the detailed response.
Yesterday, I visited below two links and based on them we've now decided to migrate the package stuff into SQl stored procedure. Once again, thank you for the response.
http://www.jamesserra.com/archive/2011/08/when-to-use-t-sql-or-ssis-for-etl/
https://bennyaustin.wordpress.com/2010/06/26/ssis-pivot-transform-vs-t-sql-pivot/
Thanks,
Ankit
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply