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 |
chbala85
Starting Member
49 Posts |
Posted - 2013-07-15 : 02:05:36
|
Hi,i have database test007DB and i need count all table rows then insert into test99 table using ssis packages .test99: tableName countRows t1 20 t2 30 t3 25 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-15 : 02:16:31
|
use catalog view INFORMATION_SCHEMA.TABLE for getting table details. Create a variable Tables of type object in SSIS to hold result inside execute sql task with statement asSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_TYPE+'BASE TABLE'and map variable in result mapping tab.Use a for each loop for iteration in SSISCreate a variable @[User::TableName] to get tablenames inside loopThen use query as "INSERT DestinationTable SELECT '" + @[User::TableName] + "',COUNT(*) FROM " + @[User::TableName] set connection to your dbexecuting package will give you table names with counts in your table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-15 : 02:32:07
|
So steps will be like1. Create a execute sql task with statement asSELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE WHERE TABLE_TYPE = 'BASE TABLE'Set CommandType = 'Full Resultset'and create an object variable called Tables and map it in variable mapping tab.2. Use a for each loop to iterate through the object variable and create a string variable TableName to get each iteration value3. create a variable sqlquery to hold query, set EvaluateAsExpression property tru and set expression as"INSERT DestinationTable SELECT '" + @[User::TableName] + "',COUNT(*) FROM " + @[User::TableName] 4. Use a execute sql task inside loopset command type as from variable and map it to SQLAuery variable created above5. execute package to get table populated with required data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|