July 7, 2011 at 7:31 am
Hi All,
Am facing an issue while inserting into table through variable.
My 1st table tbl1 has the column Sql_Query which has datas like
select 'a' as columnA, 'b' as columnB, 'c' as columnC.
Getting this query to a global variable, declared as string, User::varString returned as full result set to another variable, declared as object, User::varObject
Now trying to assign the object datatype variable to an insert statement of 2nd table tbl2 (which has same columns as in 1st table sql_query column) in Execute SQL Task returned none:
insert into tbl2(columnA, columnB, columnC)
@[User::varObject] //since User::varObject has overall resultset of the columnA,B,C
But it throwing error as
[Execute SQL Task] Error: Executing the query "insert into tbl2
(PERS_GEN_KEY, MGA..." 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.
Please if anyone come across this issue, suggest me to overcome it. :crying::ermm:
July 7, 2011 at 3:20 pm
Mind you - I'm not one of the gurus on here. Hopefully someone will correct me if I get things wrong.
To use variables in your SQL you need to create variables that evaluate as expressions. So you can't enter that statement directly.
Also, the SQL seems malformed. The returned values won't be in the correct format for SQL to insert them since they won't have the single quotes around them. And to use values in expression language SQL you need to convert numerics to strings.
Sounds like there's a few things going on here.
July 11, 2011 at 3:14 am
JustMarie (7/7/2011)
Mind you - I'm not one of the gurus on here. Hopefully someone will correct me if I get things wrong.To use variables in your SQL you need to create variables that evaluate as expressions. So you can't enter that statement directly.
Also, the SQL seems malformed. The returned values won't be in the correct format for SQL to insert them since they won't have the single quotes around them. And to use values in expression language SQL you need to convert numerics to strings.
Sounds like there's a few things going on here.
You are absolutely right about using variables in SQL, but that advice only goes up for numeric and string variables.
The OP is using an object variable, and you can only use those in a script task or a script component as a source in a dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 5:53 am
Please, could you guys explain with a sample case, since i need it in urgent. Is it possible to insert into table with select query from variable?
July 11, 2011 at 6:09 am
This is not really the way that SSIS wants you to work.
It would be better if you could create a datasource which is your SELECT query.
Then put that into a dataflow as your source and, within that dataflow, map the columns to your destination table.
As far as I can think, you cannot do a set-based insert the way you have described.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2011 at 6:12 am
Janaa (7/11/2011)
Please, could you guys explain with a sample case, since i need it in urgent. Is it possible to insert into table with select query from variable?
Yes. It is possible.
Create a dataflow. Put a script component as a source and put the object variable as read variable. Create the necessary output columns. Inside the script component, write .NET code that reads the object variable and outputs the data to the pipeline. In the dataflow, use an OLE DB Destination to write the data to the destination.
For more info about the script component:
http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/2540.aspx
Another option is that you loop over the Object variable (which is actually an ADO.NET recordset) with a For Each Loop container and execute a single INSERT statement per row. But this will be quite terrible on performance I guess.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 6:16 am
After I've read Phil's comment about the way SSIS works, I started thinking about easier alternatives, and I've found one:
* you select the SQL statement from the table and store it in a string variable
* you create another string variable that contains the INSERT part
* you create yet another string variable and set the property EvaluateAsExpression to TRUE. In the expression box, you type @[User:InsertPart] + " " + @[User:SelectPart]
This last variable will contain the entire SQL INSERT statement.
Put an Execute SQL Task on the control flow and configure the task to get the SQL statement from the variable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 6:17 am
The SELECT query is not static. It varies depends upon unique_id of sql table, that's why I tried to get the query inside the sql_column, and insert into #temp table, then from temp tried to transfer to .txt file.
July 11, 2011 at 6:22 am
Janaa (7/11/2011)
The SELECT query is not static. It varies depends upon unique_id of sql table, that's why I tried to get the query inside the sql_column, and insert into #temp table, then from temp tried to transfer to .txt file.
You are making it too complicated.
Just store the statements into string variables and dynamically create the final SQL statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 11, 2011 at 7:18 am
Thanks a lot, by two string variables varInsert+varSelect, I've done the inserting.
Thanks once again:-)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply