SSIS Variable in Data Flow Task used incorrectly

Question

In the SSIS Package I have several Data flow Tasks where I want to use one input global variable named KOERSEL to go back in time and call data set specifically in time.

When I try to run it I am getting the error:

Syntax error, permission violation, or other nonspecific error.

when I change ? to 1 in the SQL command text, the code is running fine. So what am I missing?

DECLARE @dt DATETIMEOFFSET = SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETDATE()), '-04:00')
DECLARE @interval INT = ?
SET @interval = -1 * @interval
DECLARE @DATE_OPG DATE
SELECT @DATE_OPG = A.DWH_PR_DATO
FROM TABLE AS A
WHERE YEAR(A.DWH_PR_DATO)=YEAR(DATEADD(MONTH,@interval,@dt)) AND
      MONTH(A.DWH_PR_DATO)=MONTH(DATEADD(MONTH,@interval,@dt)) 
ORDER BY A.DWH_PR_DATO DESC

SELECT DISTINCT COLUMN 1,
                COLUMN 1,
                COLUMN 1,
FROM TABLE 1
WHERE DATE_OPG=@DATE_OPG
UNION ALL
SELECT DISTINCT COLUMN 2,
                COLUMN 2,
                COLUMN 2,
FROM TABLE 2
WHERE DATE_OPG=@DATE_OPG
...

Screenshot

Answer 1

I don't think that the following error is the real issue.

Incorrect syntax near ')'.

The query parser was not able to parse the query because you have added a minus sign before the question mark ?. In this answer i will try to clarify the main cause of the error you are seeing.

Parameter data type vs Variable data type

Based on the official OLEDB Source - Documentation:

The parameters are mapped to variables that provide the parameter values at run time. The variables are typically user-defined variables, although you can also use the system variables that Integration Services provides. If you use user-defined variables, make sure that you set the data type to a type that is compatible with the data type of the column that the mapped parameter references.

This implies that the parameter datatype is not related to the variable data type.

So when you are using -? inside the SQL Command the query parser are not able to identify the parameter metadata even if it is mapped to an integer variable.

You can check my answer on the link below, it contains much details with experiments:

• Date calculation with parameter in SSIS is not giving the correct result

---

Solving the problem

(1) Force parameter data type

Try using CAST() function to force the parameter data type and assign it to a variable in the same way you have declared @dt:

DECLARE @interval INT = CAST(? as INT)
--If you want to get a negative number else ignore the row below
SET @interval = -1 * @interval
DECLARE @dt DATETIMEOFFSET = SWITCHOFFSET(CONVERT(DATETIMEOFFSET,GETDATE()),'-04:00');
DECLARE @DATE_OPG DATE;
SELECT @DATE_OPG = DWH_PR_DATEO
FROM TableName
WHERE YEAR(DWH_PR_DATO) = YEAR(DATEADD(MONTH,@interval ,@dt)) AND
MONTH(DWH_PR_DATO) = MONTH(DATEADD(MONTH,@interval ,@dt))
ORDER BY DWH_PR_DATO DESC

(2) Using Expressions

You can use Expressions while building the SQL Command:

1. Add a variable of type string (Example: @[User::strQuery])

2. Define an Expression within this variable:

   "DECLARE @dt DATETIMEOFFSET = SWITCHOFFSET(CONVERT(DATETIMEOFFSET,GETDATE()),'-04:00');
    DECLARE @DATE_OPG DATE;
    SELECT @DATE_OPG = DWH_PR_DATEO
    FROM TableName
    WHERE YEAR(DWH_PR_DATO) = YEAR(DATEADD(MONTH,-" + @[User::KOERSEL] + ",@dt)) AND
    MONTH(DWH_PR_DATO) = MONTH(DATEADD(MONTH,-" + @[User::KOERSEL] + ",@dt))
    ORDER BY DWH_PR_DATO DESC"
   

3. In the OLEDB Source choose SQL Command from variable and Select @[User::strQuery]

---

Experiments

I tried a similar query using the AdventureWorks database:

DECLARE @dt DATETIMEOFFSET = SWITCHOFFSET(CONVERT(DATETIMEOFFSET, GETDATE()), '-04:00')
DECLARE @interval INT = CAST(? as INT)
SET @interval = -1 * @interval
DECLARE @DATE_OPG DATE
SELECT @DATE_OPG = A.[ModifiedDate]
FROM [AdventureWorks2016CTP3].[HumanResources].[Employee] AS A
WHERE YEAR(A.[ModifiedDate])=YEAR(DATEADD(MONTH,@interval,@dt)) AND
      MONTH(A.[ModifiedDate])=MONTH(DATEADD(MONTH,@interval,@dt)) 
ORDER BY A.[ModifiedDate] DESC

SELECT * FROM [AdventureWorks2016CTP3].[HumanResources].[Employee]
WHERE [ModifiedDate] = @DATE_OPG 

And the query is parsed successfully

Answer 2

Instead of -? use the following logic:

-1 * (CAST(? as int))

if you just want to pass the variable as parameter without a negative sign then just use:

(CAST(? as int))

You cannot assign a negative sign to the parameter because it will cause some conflict since the query parser will not be able to define the parameter data type.

If it still throwing and exception, check the following link it contains a workaround:

• Problem With Parameter Multiplied By Negative Value In Where Clause in OLE DB Source