execute sql task error

Question

An execute sql task has been defined as below.the parameter mapping is system:Username input varchar 0 -1 System:packagename input varchar 1 -1

DECLARE @DataLoaderUsername VARCHAR(100)
DECLARE @PackageName VARCHAR(100)
DECLARE @Code VARCHAR(5)
DECLARE @RunNumber INT

SET @DataLoaderUsername = ?
SET @PackageName = ?
SET @Code = 'bbb'


INSERT INTO tBL_Log
(LoadDateTime,DataLoaderUsername,PackageName,Code)
SELECT GetDate(),@DataLoaderUsername,@PackageName ,@Code

SELECT @RunNumber = Max(RunNumber) 
FROM tBL_Log

SELECT @RunNumber As LoadID

It shows an error

Execute SQL Task] Error: Executing the query "DECLARE @DataLoaderUsername VARCHAR(100)

The same query executed in ssms and it works fine(with a hardcoded username)

Tab Alleman · Accepted Answer · 2016-07-11 12:58:58Z

1

Put semi-colons at the end of each SQL command. When SSIS sends the command to SQL Server, it sends it as a single line, so semi-colons are needed to show SQL Server where each new command begins.

answered Jul 11, 2016 at 12:58

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tab Alleman Over a year ago

There should be more to the error message. Can you post the entire message?

Shiva · Accepted Answer · 2016-07-11 20:11:34Z

Your code inside the Execute SQL Task should only contain the following.

INSERT INTO tBL_Log
(LoadDateTime,DataLoaderUsername,PackageName,Code)
SELECT GetDate(),? ,?, 'bbb'

SELECT LoadID = Max(RunNumber) 
FROM tBL_Log

In the General page, make sure you have ResultSet set to single row, and the DB connection also set to a valid connection manager.

I think you've already setup your Parameters page, just make sure they look like the following. Remember the index for the parameters starts at 0 and is specified in the Parameter Name box (Not intuitive, I know. It tripped me up in the beginning when I was learning SSIS).

Finally, in order to get the LoadId value that you are selecting in the last statement, create a variable to hold that value in the ResultSet pane. The ResultSet should be called 0.

So it looks like this.

Collectives™ on Stack Overflow

execute sql task error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related