Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HelalM
Starting Member
19 Posts |
 Posted - 2012-03-16 : 23:40:44
|
| I need to extract and combine three different data sets (all records from three data sets - UNION ALL) from SQL 2008 tables and export them into one table in SQL 2008 using SSIS package. What's the best method(s) to combine them all into one and export it to SQL table using SSIS?Thanks,HelalHM |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-17 : 00:25:22
|
| why use SSIS package for this? why not use a t-sql script for the same? are tables in different db or different server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
HelalM
Starting Member
19 Posts |
Posted - 2012-03-17 : 13:54:59
|
| well, few issues:first, I need to feed this combined data onto another application and do further analysis. Thus, next time my manager open the application, combined data should be refreshed. Though, this other app connects to SQL, but since data come from three different servers and DBs, I believe it's more efficient to do the groundwork in SQL and then point the app to this one table. Second, I am not that savvy in t-sql to write stored procedure or views to merge these data so data gets refreshed automatically. I thought SSIS is easier to use than creating tables, stored procedures, and or views. Any thoughts or guidance how to do this in t-sql?Thanks,HM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-17 : 14:04:51
|
| ok...if they're in different servers then SSIS is the way to go.you can use data flow tasks to get the task done. If you require combining data using join it might even be worth doing using execute sql task for doing merge. You might need to create staging table for the same. you can create it at the destination db itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
HelalM
Starting Member
19 Posts |
Posted - 2012-03-17 : 15:30:30
|
| when you say sql task, what does it do? do I need to write sql to combine all data first before using sql task? or sql task walks me through connecting to dbs and extracting data?thank you so much for your helpHM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-18 : 10:46:04
|
quote:
Originally posted by HelalM
when you say sql task, what does it do? do I need to write sql to combine all data first before using sql task? or sql task walks me through connecting to dbs and extracting data?thank you so much for your helpHM
by sql task i meant execute sql task------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Combine 3 different data sets into one and export