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