1

I've this query in my OLE DB Source, using a parameter inside it:

select *
from A
where A.A_DATE >= DATEADD(d,-1*(CAST(? as int)),GETDATE())

with parameter X used. For sample case, I use X = 1. If I run the above query in the SQL Server Management Studio, it returns a row (the correct one). But when I run it inside the SSIS package it doesn't return any row (which is incorrect). When remove the -1*xxx and go straight with the CAST(? as int) (the query looks like this:)

select *
from A
where A.A_DATE >= DATEADD(d,CAST(? as int),GETDATE())

and set the X value to -1, it shows the correct result (a row returned). Is there something wrong with the parameter multiplication with a negative value inside an OLE DB Source query?

update 1: It seems there is another problem with the 2nd query, as if I change the value to 1, I still get row results (when it shouldn't). Any solutions??

update 2: it seems the cast solution is used is also flawed, since it doesn't return the correct value either. I kept getting 2 rows returned when I should have only 1 row returned

Seems the query above is also flawed in SSIS, so I decided to use this query, so I don't have to use CAST:

"select *
from A
where A.A_DATE >= DATEADD(d," + @[User::Y] + ",GETDATE())"

and put it inside a variable (let's called it Src_Query) and then evaluate the string above as an Expression. I also used variable Y with String data type instead of X with data type Int32 I used previously, and to turn it to negative value I just used Script Task to deal with it. Then in the OLE DB Source I used the "SQL Command from variable" option. I run the package, and the query returned the correct result. It's also useful for variable that I used inside sub-queries.

The problem with the above solution: In my project I have some source query that has more than 4000 chars, and SSIS doesn't allow more than 4000 chars processed in the Expression Builder. Still searching a way to go around this problem.

Update 1: I've workaround my problem for more than 4000 chars-long query, by putting the where clause in a separate Conditional Split after the data retrieval, and I still can use my Int32-typed variable.

This is the expression that the conditional split used, with the query in OLE DB Source no longer has the where clause related to the date:

A_DATE >= DATEADD("d",@[User::X],(DT_DBDATE)GETDATE())

Wonder how it will affect the performance though, is it significant?

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
rpa
  • 97
  • 1
  • 9
  • How are your parameters set up in Set Query Parameters and how are the SSIS variables defined? – Wil Sep 23 '11 at 01:58
  • the variables is set up normally in the set query parameters, with the default Parameter0 mapped to User::X, and the parameter X itself is defined as a variable with data type Int32 and the whole package as its scope, and the value is added directly, no custom task done for the variable – rpa Sep 23 '11 at 03:24
  • Then why do you need to cast the parameter as INT? It should be compatible with the values as is. For sure I think it's the interaction between the `CAST` and the negative value. – Wil Sep 23 '11 at 05:50
  • if I don't use the CAST, there will be an error when I'm trying to exit the OLE DB Source editor. The error is these two: "Statement(s) could not be prepared." and "Argument data type datetime is invalid for argument 2 of dateadd function.". I found one of the solution is to cast it, as putting the "?" directly will result in the above error – rpa Sep 23 '11 at 06:30
  • 1
    But you said earlier that the variable has a datatype of INT32...the OLEDB source editor is complaining that datatype datetime isn't compatible with argument 2 of DATEADD. Check your parameter mappings and their datatypes. – Wil Sep 23 '11 at 14:12
  • Rechecked it and i still can't put the ? directly inside the DATEADD in the WHERE clause. the error still came up unless I use CAST (haven't found any other solution for this). Still wondering though why the SSIS is recognising my variable as a datetime, when I'm 100% sure it's stated Int32. Now I'm using script task to turn my positive-valued variable (user's requirement) to negative so I don't have to multiply it with -1. @Wil : you might want to try the above case yourself, to know whether you've encountered similar error or not. – rpa Sep 26 '11 at 01:03

0 Answers0