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)

Answer 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.

Answer 2

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.