| Author |
Topic |
|
tross
Starting Member
20 Posts |
 Posted - 2011-05-29 : 09:12:14
|
| I new to the ssms pgm to create ssis packages.1) I need to read a table/query which contains table names (about 400 tables) from another DB (DB1).2) loop thru these names and load these table into one table in the datawarehouse DB (DB2) using a ssis package.( table structure is the same in both DB's)I've not been able to figure out how to do this.I'll need detailed example, because I've mainly done admin work and now I need to start learning how to built ssis packages.(i've tried reading a view of each table and it take over 3 hours, we timed a ssis package and a view and found that the ssis package loaded 900,000 rows in 5 minutes, the view?? was still running after 30 minutes.)so, I need to know how to do this thru a ssis packageTIA |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-31 : 10:43:34
|
| I think, if you have a query or a table which can return all the table names which you need. Then,1.open a ssis package,in control flow take execute sql task, execute the query or the perform "select tablenames from the table". Store the resultset to a variable of type object.2. Take a for each loop,drag and drop data flow task,in it take OLEDB source consider the option SQL QUERY from the variable then map the resultset variable to your query.3. Take your destination and load the data. |
 |
|
|
tross
Starting Member
20 Posts |
Posted - 2011-06-01 : 16:13:01
|
| I get an error that it's looking for a tablename.it does not like the following. (in the source qry of the dataflow)select * from @[user::TableNameRow]How do you add screen prints???I can show you what I have.Tim |
|
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-06-02 : 13:14:59
|
| select Column(s) from " + @[user::TableNameRow]+ "specify the column names.check these link for more info:http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx |
|
|
|
tross
Starting Member
20 Posts |
Posted - 2011-06-03 : 08:22:47
|
| it still is looking for the table name after the from.I wish I could add an attachment or screen copy .I dont have a clue on how to do this.(where and what variable do I add)(how do I build the flow control correctly)TIM |
|
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-06-03 : 11:09:37
|
| Let us consider that you have 4 tables in one database 'A' and you want to store the data from these 4 tables to a single table in other database'B'.If so, i have a question here, is the 4 tables have same columns with same data types.If the no.of columns change then they might be problem in dynamic connection establishment in SSIS.What i suggest is:1. Use import/export wizard in SSMS(In management studio-->database'A'--right click and go to Tasks-->Export Data.2. Save these package then make necessary changes to import all 4 tables.let me know if any errors |
|
|
|
tross
Starting Member
20 Posts |
Posted - 2011-06-03 : 12:12:54
|
| yes all 4 source tables are the same.if I load one table using the ssis package with a hardcoded name it will work.when I change the hardcoded name to a variable the parser indicates that it wants a table name after the FROM.I'm not able to figure out what I'm doing wrong.and I can't get any of the error msg to copy so I can paste it here.and I know there will be something I leae out if I try retyping.TIM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-06-03 : 13:42:07
|
| you need to use sql command from variable option in oledb source and then form query separately in a variable created inside ssis using evaluate as expression property set to true and expression set to get correct value.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
dynamic ssis package to load multi database files