A.W.M.


Hello All

I was trying to insert some row from one table to another of different database.

I was using Execute SQL task along with Foreach loop container.

In my execute SQL task I am using this query

SET IDENTITY_INSERT dbo.Table1 ON

INSERT INTO dbo.Table1

SELECT * FROM DB2.dbo.Table2
WHERE TableKey =

When executed I get this error:

failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

While the same query when executed in Management Studio Its successful.

The properties I set

For Each Loop Editor Settings:
1) Collection: a) Enumerator Set to ForEach ADO Enumerator
b) ADO Object Source Variable: User:SurprisebjectVariablename
c) Checked Rows in the first table
2) Variable Mapping: New Int Variable2 and Index = 0 to set it to first colunm.
3) Expression: Left blank

Execute SQL Task Editor:
1) General: a) Timeout : 0
b) CodePage: 1252
c) Result Set: None
d) SQLSourceType: Directinput
e) SQL Statement: SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1 SELECT * FROM DB2.dbo.Table2 WHERE TableKey =
f) BypassPrepare: False
2)Parameter Mapping: Variable Name : New Integer variable2 selected
Direction: Input
DataType: Long
ParameterName: 0

Can somebody help me in this regards.

Reference:

a) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx





Re: Insert failure while using ForEach Loop + Execute SQL task.

desibull


There are a couple of things. First, you need to set the Result Set to "Single Row". You then need to write the output to an ADO Record Set which has to be stored in a variable of type "Object". You need to pass the output to the variable via the Result Set box; the ResultName should be '0' and the variable name should be the name of the variable you created.

Your ForEach Loop is set correctly.






Re: Insert failure while using ForEach Loop + Execute SQL task.

desibull

One more thing.

Set the Result Set to "Full Result Set" if you are expecting multiple rows from the select statement. I am guessing this is why you are using a ForEach loop






Re: Insert failure while using ForEach Loop + Execute SQL task.

A.W.M.

Yes I am looping over rows and set the execute SQL task to the setting given.

Those setting were given to the execute sql task which I was using before ForEachLoop container in Control Flow.

The setting which I am doubtful is of the execute SQL task which I am using INSIDE teh for Each loop container.

When I am breaking the package I clearly see taht the variables is being populated with the value it shud b. So the whole question is about the settings I made in Execute SQl task which I am using in the For Each Loop container. Since i dont need any result set and I just wanted to execute a query that why I set the resultset to NONE and to get the value of in my query I have call in the varible using parameter mapping. If seen over view i see no error in my settings, but still ther is one which I am not able to figure out.






Re: Insert failure while using ForEach Loop + Execute SQL task.

desibull

OK, maybe it does not like the * in your select statement. Why don't you try explicitly specifying the first column. When you specify * the statement is obviously going to return multiple columns, and if all you are interested is in the first column just state it explicitly.





Re: Insert failure while using ForEach Loop + Execute SQL task.

desibull

I aplogize AWM.. Please ignore my recent post. I re-read your question and my response for not correct.

In looking at the settings for the variable you have the DataType set to "Long". If you are passing it to the TableKey you might want to try setting it to "Integer".

Again, sorry for the confusion. I should take more time to read the posts completely.





Re: Insert failure while using ForEach Loop + Execute SQL task.

A.W.M.

I have had tried both of them before. Selected all colunm names instead of * and Changing variables to Integer instead of LONG. All those possible changes I tried but still I got that error. After trying all posible solution I decided to put this in Forum




Re: Insert failure while using ForEach Loop + Execute SQL task.

Rafael Salas

What is the data type of TableKey column; make sure it is compatible with the data type of the variable that maps to





Re: Insert failure while using ForEach Loop + Execute SQL task.

A.W.M.

Rafael:

I used the same Data type to update the same table. It works fine while being updated. Except when I insert it doesn't work. Except the change in SQL Statement I kep all the settings similar. Thats what bother me. When I update it works but when I am inserting it doesn't. and gives me this error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The query I am using is

SET IDENTITY_INSERT dbo.Table1 ON

INSERT INTO dbo.Table1
(Col1,

Col2,

col3)


SELECT
Col1,

Col2,

col3

FROM

DB2.dbo.Table2
WHERE Table2Key =

The query I sued to update the same table. Which WORKED

UPDATE dbo.Table1 SET Table1Flag = 'N' WHERE Table1Key = AND Table1Flag = 'Y'






Re: Insert failure while using ForEach Loop + Execute SQL task.

A.W.M.

Thanks a lot Group. I got the solution.

Instead of using Execute SQL task I used and Data flow task and it worked.

I guess while using Execute SQL task in my connection I specified DB1 and in my SQL Statement I was Selecting from DB1 but I was inserting in DB2. I think here was the error. Might be SSIS didn't understannd DB2 as it was not specified in the connection. Thats why might be it got failed.

I used Data flow task isntead and when I specifeid the source and destination with source being set up with the variable calling from parameters and It worked.

Thanks a lot folks..