We ran into a major issue with Integration Services, which caused us a major scare, so I wanted to share it.
We had a query, which was pulling out a list of items to compare to determine what to load. This list was generated with a simple query, where the date was calculated like this:
select * from table where date > getdate() - ?
The question mark was a parameter, being replaced with an integer value of 5. We didn’t notice any immediate issues. When we changed the integer to 1, we noticed that the amount of data returned decreased drastically, down to almost nothing. We began running checks, and found out the following issues:
- SSIS dynamically determines the parameter type at run time. For this query, even though we are using an Integer Variable, it is being converted to a date time (essentially a cast(1 as datetime))
- The date being passed, was not getdate() – 1, but instead appeared to be getdate()+1
Number 1 was not very affecting, after all in SQL 2008 and 2012, if you do a getdate() – cast(1 as datetime) it returns the correct result, but Number 2 was severe with small time frames, and hidden in our production environment, because the dates were buffered because the value of the parameter was 45.
But what was causing Number 2? Profiler revealed that the query being passed was correct, but after further investigation the parameter being passed is different.
In SSIS, the integer 1, converted to a date time is: 1899-12-31 00:00:00
In SQL Server, the integer 1, converted to a date time is: 1900-01-02 00:00:00
Essentially, the query performed as expected, but the logic of cast(1 as datetime) was different for the two systems. To solve this, there are two solutions we have found so far, one which we believe is superior to the other. First you can replace the query in the OLEDB Data Source with a SQL Command from Variable, and build the string to pass to SQL Server. This solves the issue, but it does make diagnosing query issues in the future harder. Second, you can do the date calculation in SSIS (Variable, Evaluated as Expression), and then rewrite the statement as:
</pre> <pre>select * from table where date > ?</pre>
This solution is more elegant, as you still have everything else intact, and only a single variable is created, rather than one variable for each SQL Statement. Also it allows queries longer than 4000 characters.
ERMAHBLERG 