| Author |
Topic |
|
santkris4
Starting Member
10 Posts |
 Posted - 2011-02-09 : 12:01:54
|
| Currently in my organization, the person who used to design databases, has written a lot of data migration scripts for a new database. The new database needs to be populated by referring three other databases outside. The scripts do not have any sequence and assumes that the outside servers are created as linked servers. The script runs to around 3000 to 3500 lines of code.I was thinking (since I have some time)of refactoring the script into a SSIS workflow, so we could keep it organized and becomes easier to maintain. So my plan is to define workflows for the transformations from different sources (the usual SSIS stuff) and also have static insert statements for where applicable.Am I right with the above approach? Is SSIS a good option? Maintaining script files is just very frustrating for me. Iam planning to execute the SSIS package in all environments going up the chain (TST, QA, Staging, Prod etc).All comments/ feedback are welcome!! |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-09 : 12:06:57
|
SSIS makes it easy, because you can leverage variables such that you only have 1 package, and use variables for the source/destination servers. You can also , as you say, maintain it and modify it a little bit easier. You can skip the insert statements and use Data flows that can be optimized. Also, you can avoid the linked server issue. SSIS would be faster I am pretty sure...
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-09 : 13:24:02
|
It is a matter of taste.I am avoiding SSIS whenever it is possible.Why?Because I don't like the way of developing a package and then deploying it.Because I don't like the way of clicking many times to find the way where the code is implemented.Because I don't like the way SSIS is throwing unhelpful errors.And so on.But that is only my humble opinion!I love to code in T-SQL and I like to open a script file and see directly what is going on.(I am doing big data migrations since about 7 years)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
santkris4
Starting Member
10 Posts |
Posted - 2011-02-09 : 13:56:28
|
Thanks. When you say "You can skip the insert statements and use Data flows that can be optimized.", could you explain that a little please? I thought of having insert scripts embedded within the SSIS flow rather than having separate .sql files because these data are not available from any source. We have manually insert this as part initialization.quote:
Originally posted by dataguru1971
SSIS makes it easy, because you can leverage variables such that you only have 1 package, and use variables for the source/destination servers. You can also , as you say, maintain it and modify it a little bit easier. You can skip the insert statements and use Data flows that can be optimized. Also, you can avoid the linked server issue. SSIS would be faster I am pretty sure...
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-09 : 14:36:45
|
You can have the scripts inserted into the SSIS package as Script tasks or SQL tasks. But you can also just have data flows from source >= destination. It will make more sense if you actually look into using it. I am not sure what you mean by manually insert this ...I was just making a general statement about the data flow in an SSIS.Webfred makes a good point too...I write more T-SQL than I do SSIS as well, and find in a lot of cases it is pretty easy to use and maintain...There are advantages to both..you might want to evaluate the time involved to "convert" from all T-SQL into SSIS, and the end resulting benefit.As he said, it is matter of taste.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
santkris4
Starting Member
10 Posts |
Posted - 2011-02-09 : 16:25:27
|
Thanks!! It makes sense now.Also with respect to the T-SQL approach, what do we do when we are dealing with millions of rows. Do we generate insert scripts for that? Doesn't that sound error prone?quote:
Originally posted by dataguru1971
You can have the scripts inserted into the SSIS package as Script tasks or SQL tasks. But you can also just have data flows from source >= destination. It will make more sense if you actually look into using it. I am not sure what you mean by manually insert this ...I was just making a general statement about the data flow in an SSIS.Webfred makes a good point too...I write more T-SQL than I do SSIS as well, and find in a lot of cases it is pretty easy to use and maintain...There are advantages to both..you might want to evaluate the time involved to "convert" from all T-SQL into SSIS, and the end resulting benefit.As he said, it is matter of taste.
Poor planning on your part does not constitute an emergency on my part.
|
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-09 : 16:48:07
|
My personal opinion, use database copy in SSIS instead because it may perform faster for the whole database. Going structure by structure in T-SQL would mean that only 1 table will go at a time. In SSIS, you could use SQL tasks that run in parallel (or do Data flow steps that move the data that can run in parallel) which may cut down on the time involved. Small data with linked servers, I use T-SQL. Large data with many tables I might lean toward SSIS.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
santkris4
Starting Member
10 Posts |
Posted - 2011-02-09 : 18:41:28
|
Thanks. I agree sometimes it comes down to personal preference.quote:
Originally posted by webfred
It is a matter of taste.I am avoiding SSIS whenever it is possible.Why?Because I don't like the way of developing a package and then deploying it.Because I don't like the way of clicking many times to find the way where the code is implemented.Because I don't like the way SSIS is throwing unhelpful errors.And so on.But that is only my humble opinion!I love to code in T-SQL and I like to open a script file and see directly what is going on.(I am doing big data migrations since about 7 years)
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
|
Is SSIS the right way?