Parameter SQL Insert into Statement SSIS Package

3
votes

I have an SSIS package that has an Execute SQL Task where I have a INSERT Statement that inserts log data to this ssis tracking table in smss that tracks the time of execution. Everytime I try to execute the task I get this error: [Execute SQL Task] Error: Executing the query "INSERT INTO dbo.SSIS_Logging_Details (Execution_In..." failed with the following error: "Must declare the scalar variable "@ExecGUID".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

1.This is the Statement I have in the SQLStatement field in the Execute SQL task:

INSERT INTO dbo.SSIS_Logging_Details
          (Execution_Instance_GUID, Package_Name, Execution_Date)
          VALUES (@ExecGUID, @PackageName, GetDate())

2.I have the these parameters in the Parameter Mapping tab of the Execute SQL task: enter image description here

3.For the SQL table in smss:tableSSIS_Logging-Details here are the fields:

Execution_Instance_GUID nchar(100) NULL

Package_Name nchar(100) NULL

Execution_Date datetime NULL

4.And for the 'Configure SSIS Logs: Package' I have the package in there with the provider type being SSIS Log Provider for SQL Server (but it keeps switching to SQL Server Profiler for some reason whenever I open it..) enter image description here

2
sqlssis

2 Answers

6
votes

You don't use the variable names in the query in an execute sql task. You replace them with question mark placeholders, like this:

INSERT INTO dbo.SSIS_Logging_Details
          (Execution_Instance_GUID, Package_Name, Execution_Date)
          VALUES (?, ?, GetDate())

In your parameter mapping, instead of parameter names, use their position starting with 0 for the first parameter. So replace @ExecGUID with 0 and replace @PackageName with 1.

1
votes

You could also write it in this way:

DECLARE @ExecGUID NCHAR(100)
DECLARE @PackageName NCHAR(100)

SET @ExecGUID = ?
SET @PackageName = ?

INSERT INTO dbo.SSIS_Logging_Details
   (Execution_Instance_GUID, Package_Name, Execution_Date)
VALUES (@ExecGUID, @PackageName, GetDate())

I use this format, because I find it easier to test in Management Studio. Rather than looking for the ?, this will keep all your assignments in one spot, especially if you have a large number of lines in an execute sql task.

You would still need to change the parameter mapping as @Tab Alleman mentions in his answer.