Execute SQL Task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Execute SQL Task

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-13 : 10:36:45

Hey all
I have created an SQL Task with the following as SQL Statement Source expression:

"INSERT INTO SSISLog (EventDate,StaffNo, EventType, EventDescription)
VALUES (
'@[System::CreationDate]+',
' +@[System::SourceName]+' ,
'OnError',
'+ @[System::ErrorDescription] +'
)
"

And I get the following error:

[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDate,StaffNo, EventType, EventDescription) VALUES ( '@[System::CreationDate]+', ' +@[System::SourceName]+' , 'OnError', '+ @[System::ErrorDescription] +' ) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any pointers will be highly appreciated. Oh and the purpose of this is to put error logs into the SSISLog table.

Many thanks

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-13 : 10:38:18

quote:
Originally posted by Rupa

Hey all
I have created an SQL Task with the following as SQL Statement Source expression:

"INSERT INTO SSISLog (EventDate,StaffNo, EventType, EventDescription)
VALUES (
' + @[System::CreationDate]+',
' +@[System::SourceName]+' ,
'OnError',
'+ @[System::ErrorDescription] +'
)
"

And I get the following error:

[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDate,StaffNo, EventType, EventDescription) VALUES ( '@[System::CreationDate]+', ' +@[System::SourceName]+' , 'OnError', '+ @[System::ErrorDescription] +' ) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any pointers will be highly appreciated. Oh and the purpose of this is to put error logs into the SSISLog table.

Many thanks

Rupa

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-13 : 10:45:04

Harsh!! Hey!! Hope ur gd...Tried that..Still brings up same error!!

Thanks,
Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2007-06-13 : 10:49:19

Solved that!!! Had an error in the Parameter mapping. BUT..Now I get this error:

[Execute SQL Task] Error: Executing the query "INSERT INTO SSISLog (EventDate,StaffNo, EventType, EventDescription) VALUES ( '@[System::CreationDate]+', ' +@[System::SourceName]+' , 'OnError', '+ @[System::ErrorDescription] +' ) " failed with the following error: "Syntax error converting datetime from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Tried all sorts of conversion techniques like DT_DATE but it won't work. Any ideas?

Thanks,
Rupa

sowmya.br
Starting Member

5 Posts

Posted - 2008-04-23 : 10:44:14

Hi,
Could you please help me in using user defined variable in delete and update statement in Execute SQL task in SSIS package.
I am getting the error when i try to run the following delete statement "DELETE FROM tbl_import_exchange_rate_test
WHERE (ifxr_report_date = ' + @[User::dt_reportingdate] +'"

The error is
[Execute SQL Task] Error: Executing the query "DELETE FROM tbl_import_exchange_rate_test WHERE (ifxr_report_date = ' + @[User::dt_reportingdate] +'" failed with the following error: "Line 2: Incorrect syntax near ' + @[User::dt_reportingdate] +'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 12:38:48

Try like this:-
'DELETE FROM tbl_import_exchange_rate_test
WHERE ifxr_report_date = ''' + @[User::dt_reportingdate] +''''

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-23 : 12:39:45

Also make sure you cast it to date type if its of type string

'DELETE FROM tbl_import_exchange_rate_test
WHERE ifxr_report_date = ''' + (DB_DATE) @[User::dt_reportingdate] +''''

sowmya.br
Starting Member

5 Posts

Posted - 2008-04-24 : 02:27:19

Hey Thanks,
now still i am getting the error saying "Syntax error converting Datetime to string "
If i Change the variable datatype from datetime to string then i get another error "[Execute SQL Task] Error: Executing the query "DELETE FROM tbl_import_exchange_rate_test WHERE (ifxr_report_date = ''' + (DB_DATE) @[User::dt_reportingdate] +''')" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DATE)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. "

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 02:33:39

What is datatype of @[User::dt_reportingdate] ?

sowmya.br
Starting Member

5 Posts

Posted - 2008-04-24 : 03:59:29

The data type is datetime and the COLUMN IN THE TABLE IS ALSO HAVING DATETIME DATATYPE
Do i need to do parameter mapping for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 04:27:06

Try this too:-
"DELETE FROM tbl_import_exchange_rate_test
WHERE ifxr_report_date = '" + (DT_WSTR, 20) (DT_DBDATE)@[User::dt_reportingdate] +"'"

sowmya.br
Starting Member

5 Posts

Posted - 2008-05-06 : 02:36:29

hey thanks , it did work ..
I have one more doubt, can we get the file names from SQl table ie instead of having hard coded value in flat file connection i need to tak the file name and the path from a databse table. Can i do like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-06 : 04:00:05

quote:
Originally posted by sowmya.br

hey thanks , it did work ..
I have one more doubt, can we get the file names from SQl table ie instead of having hard coded value in flat file connection i need to tak the file name and the path from a databse table. Can i do like this?

Yup you can.Create a variable to hold the table fields returned from query as a record set. Use the record set mapping tab of EXecute SQL task to assign result of query to record set. Then use a ForEach loop with ADO enumerator option to execute the transfer for each item in query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources