SSIS better approach for multiple transformations

0
votes

I'm new in SSIS. I have package with 1 data flow but with many components for transformations and its hard to read. Now, instead of 1 data flow, I'd like to have 3 or more data flows so I could separate each transformations.

1st Data Flow: Get data from table then perform small transformations then load to staging table.

2nd Data Flow: Get data from staging table and then perform transformations. Truncate the staging table and load the new data to staging table.

3rd Data Flow: Get data from staging table again and then perform transformations. Truncate the staging table and load the new data to staging table again.

4th: Data Flow: and so on...

Is it a better approach? Let me know if there's a better approach than this. I am loading 1 million of records.

Thank you!

1
ssisdataflow
"Better" is a very general term. If having multiple data flows is easier for you to understand and maintain that's certainly better. However, needing to maintain multiple staging tables can be a lot of effort. IMHO, maintenance and performance are good criteria for 'better' – Nick.McDermaid
Keep in mind that if each data flow needs to wait for the prior one to finish (if you are populating staging tables at each step then it does have to wait), this is going to take a lot longer than it needs to. You might be able to do the entire process without any staging tables. You should experiment and discover yourself – Nick.McDermaid
i want to load data to object variable, then use it as source using script source component. in script its actually reading one by one from that variable and output it. Is that good idea? thats based in my research. ill implement if it's good idea. thanks! – user1805220
In my opinion, when you need to use scripts and object variables in SSIS, you're probably using the wrong tool. Again, this is a slow approach but you need to experiment to discover for yourself. I can't really comment further without knowing what you're trying to do. It's quite possible you can do all you need with just some T-SQL. That would be the ideal solution for me, but may not be the ideal solution for you if you are more comfortable in C# than T-SQL – Nick.McDermaid
Thanks Nick. I'm comfortable with tsql so ill just put it in stored procedure – user1805220

1 Answers

1
votes

A better approach would be to replace the 2nd, 3rd and so on dataflows with stored procedures to do the transformations in the table. A dataflow can't use the same table as both the source and the destination with a truncate in the process.