SSIS execute SQL
-
I am running SSIS >> Execute SQL task with Direct sql Statement , connected to MySQL via ADO.Net as
CREATE TABLE Test_abc (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL
)
Insert into Test_abc(title) values('abc');
but when I run the package, following error is displayed
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Any solution please....
-
If you run the task after removing the INSERT line, do you get the same result?
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 -
I think you want IDENTITY not AUTO_INCREMENT
-
Thank you for your replies. I add 'GO' in between two queries and it worked perfect. I tried adding semi column as well in between Create and insert and that didnt work.
Now I want to understand why it worked with "GO" why not with semi column.
CREATE TABLE Test_abc (
task_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL
)
GO
Insert into Test_abc(title) values('abc'); -
What is a semi column?
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 - Phil Parkin wrote:
What is a semi column?
A half column; a column bisected longitudinally, or along its axis.
A low-relief, vertical pillar or pilaster in a wall, usually without a base or capital; used in architecture to divide a façade or other wall surface optically.
- Saira wrote:
Now I want to understand why it worked with "GO" why not with semi column.
I just noticed that you are using MySQL so I really don't know, but GO is the default batch separator in SSMS so I assume that SSIS also understands it to be a batch separator. A semicolon is part of the sql syntax and terminates a batch, it does not tell SQL to execute the rows above before considering the rows below.
My guess is that SSIS attempted to validate the entire query at run time and failed because you cannot insert into a non-existent table. With the GO, It probably ignored everything below the GO until it came time to execute the second batch in the execute sql task.
It might be an interesting test to deliberately put a syntax error in the insert statement and see whether the table is created by the package before it fails, or whether SSIS does validate the code below the GO.
The solution may be to keep the tasks separate. A SQL Server connection task will not fail to validate an insert into a table that doesn't exist, but maybe an ADO connection is different.
-
I mean ;
-
Thank you Ed B , yes this all makes sense .
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply