insert records into dynamically get table using ssis
I have list of tables in excel. Using this list I want to insert records from source database to destination database with the same tablename. I tried Excel Source to load into Recordset destination. Using DataViewer, the data is apssing correctly. Then After Recordset destination, I have Foreach loop to insert records for each table from source table to destination table (same table name) and different location (from source datbase to destination database). Within Foreach loop, I have Execute SQL task which has a dynamic Sql Statement: insert into [dbo].[@tablename] select * from [efi_dot_com].[dbo].[@tablename] Resultset: Single row ConnectionType: OLE DB SQLSourceType: Direct input Get an error [Execute SQL Task] Error: Executing the query "insert into [dbo].[@tablename] select * from [ef..." failed with the following error: "Invalid object name 'efi_dot_com.dbo.@tablename'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I created a variable Tablename with object type. I have not created a variable to hold sql statement. Do I need it? If so woudl you please explain to me?
August 21st, 2012 5:35pm

you can do it in this way; create a string package variable and name it as User::SqlCommand then in expression property of that variable write this expression: "insert into [dbo].["+@[User::tablename]+"] select * from [efi_dot_com].[dbo].["+@[User::tablename]+"]" Then in Execute SQL Task set sql source type as variable and set User::SqlCommand variable there. set resultset property to None (because this query doesn't have any result set)http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 5:45pm

I got an error [Execute SQL Task] Error: Executing the query "insert into [dbo].@[User::tablename] select * from..." failed with the following error: "Incorrect syntax near '@'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. the variable is Name: SQLCommand value: insert into [dbo].@[User::tablename] select * from [efi_dot_com].[dbo].@[User::tablename] the source database is define under connection string. What is wrong with the syntax?
August 21st, 2012 6:22pm

where did you wrote the expression I suggest? you should write that in expression property of SqlCommand variable and then set EvaluateAsExpression property of that variable to true and then set sql source type in execute sql task as variable and set sql statement variable as User::SqlCommand Did you tried all of those?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 6:37pm

When I use "insert into [dbo].["+@[User::tablename]+"] select * from [efi_dot_com].[dbo].["+@[User::tablename]+"]" I got similar error: [Execute SQL Task] Error: Executing the query ""insert into [dbo].["+@[User::tablename]+"] select..." failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Tablename is defined as object. I used this variable only in insert sql statement.
August 21st, 2012 6:43pm

Tablename is defined as object. I used this variable only in insert sql statement. why tablename is defined as object? does it same variable that you load data rows in RecordSet destination? and what is the variable name that you fetched in the Foreach Loop container's variable mappings tab?http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 6:50pm

When I tried Evaluate expression, I got Expression cannot be evaulated. The data type of variable "User::Tablename" is not supported in an expression. Reading the variable "User::Tablename" failed with error code 0xC00470D0
August 21st, 2012 6:52pm

When I tried Evaluate expression, I got Expression cannot be evaulated. The data type of variable "User::Tablename" is not supported in an expression. Reading the variable "User::Tablename" failed with error code 0xC00470D0 could you answer my previous post's questions pleasehttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 6:56pm

Sorry when I replied, I did not get your last thread. why tablename is defined as object? does it same variable that you load data rows in RecordSet destination? Because the RecordSet Destination variable Name set to User::Tablename and what is the variable name that you fetched in the Foreach Loop container's variable mappings tab? Collection: Enumerator is Foreach ADO Enumerator ADO object source varibale: User::TableName Enumeratation mode: Rows in the first table variable Mappings: User::Tablename index =0 Thanks
August 21st, 2012 7:06pm

OK, problem is that you used same variable for list and each iteration. do it in this way: change name of variable TableName to TablesList, and leave its data type as object. create another variable in package scope of type string and name it as User::TableName. set a default value for this variable for example one of your table's name. set the recordset destination variable name as User::TablesList set the foreach enumerator as ADO, and ado object source var as User::TablesList in variable mappings tab of foreach loop container; set variable as User::TableName, and set index as 0. then do what I said about SqlCommand variable as follows: create a string package variable and name it as User::SqlCommand in package scope then in expression property of that variable write this expression: "insert into [dbo].["+@[User::tablename]+"] select * from [efi_dot_com].[dbo].["+@[User::tablename]+"]" set EvaluateAsExpression property of SqlCommand variable to true. Then in Execute SQL Task set sql source type as variable and set User::SqlCommand variable there. set resultset property to None (because this query doesn't have any result set) let me know if you have any problemhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 7:16pm

Thank you so much. It worked. There was a problem with An explicit value for the identity column in table error. I tried to fix with SET IDENTITY_INSERT <tablename> ON After insert SQL Statement: SET IDENTITY_INSERT <tablename> OFF The problem is some of the table has identity column and others do not. If you know a pssoble solution for all tables, it would be great. Thanks
August 21st, 2012 9:05pm

you may try methods like this: http://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server To check if Idnentity insert is On, then set it to OFF, otherwise nothing.http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2012 9:17pm

you may try methods like this: http://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server To check if Idnentity insert is On, then set it to OFF, otherwise nothing.http://www.rad.pasfu.com
August 21st, 2012 9:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics