1

I have a source query, wherein I am fetching data from Horton using ODBC connection

Select * from Table1 Where CreationDate > '2020-09-24 00:00:001'

When I run this query manually it runs fine, but when I run my SSIS Package no data is being fetched.

Please note I am passing this SQL Query from a variable and all variables are correctly passed. I have checked it using Edit Breakpoints.

This are the steps I followed to pass the query from a variable

Mario Codes
  • 689
  • 8
  • 15
saurish das
  • 11
  • 3
  • Same query works in one product but not another becomes a problem of identifying "what is different?" Are you using the same credentials? If you remove the dynamic query bit and just hard code the above query in SSIS, does that return data? Are you using the exact same source? etc – billinkc Oct 26 '20 at 16:08
  • Same credentials : Yes If I hard code the query , does it work : Yes – saurish das Oct 26 '20 at 16:13
  • Ok, good, then there is something going awry with either the dynamic query being built or the mechanics of how the query is being passed, yeah? I know you specified that you checked the query with breakpoints but I'm just going with the remaining culprits. – billinkc Oct 26 '20 at 16:25
  • When you hard coded the query and it worked, how did you do that? Was the text literally in the Query Source or was the ODBC source component using a Variable which no longer had an Expression on it? – billinkc Oct 26 '20 at 16:26
  • When I hard coded it, I wrote the query Select * from Table1 Where CreationDate > '2020-09-24 00:00:001' in the ODBC Source SQL Command, and ran the Data Flow. It fetched 2 Million records – saurish das Oct 26 '20 at 16:34
  • So now if you put the same query into an SSIS Variable and then modify the Data Flow's Expressions to use the same hard coded query, does it work? Now that you've got 10+ reputation you can add screenshots to your question - which are helpful with the way SSIS works – billinkc Oct 26 '20 at 16:47
  • So now if you put the same query into an SSIS Variable and then modify the Data Flow's Expressions to use the same hard coded query, does it work? : Yes Well now I found out something. The Date Value I am passing is 2020-10-21 10:23:464 But in the final query the value that is getting passed is '10/21/2020 10:23AM' Can this be a problem? – saurish das Oct 26 '20 at 17:09
  • You can find out if the date format is the culprit by running the successful query with the mm/dd/yy format string and I fully expect it's going to fail. – billinkc Oct 26 '20 at 17:30

1 Answers1

0

The issue with your query appears to be the default formatting for translating an SSIS DateTime type to a string representation. The ODBC source appears to need a YYYY-MM-DD hh:mm:ss:mss (where mss is milliseconds but not the correct format code).

I tend to break my long expression into multiple variables and then have a "simple" final form that puts them all together.

Date_YYYYMMDD -> (DT_WSTR, 4)  YEAR(@[System::ContainerStartTime]) + "-" + RIGHT("0" + (DT_WSTR, 2)  MONTH(@[System::ContainerStartTime]),2)  + "-" + RIGHT("0" + (DT_WSTR, 2)  DAY(@[System::ContainerStartTime]),2)

That builds my YYYY-MM-DD format string. Data type is String

Date_HHMMSSms -> RIGHT("0" + (DT_WSTR, 2) DATEPART( "Hour", @[System::ContainerStartTime] ),2) + ":" +  RIGHT("0" + (DT_WSTR, 2) DATEPART( "Minute", @[System::ContainerStartTime] ),2) + ":" +  RIGHT("0" + (DT_WSTR, 2) DATEPART( "Second", @[System::ContainerStartTime] ),2) + ":" +  RIGHT("0" + (DT_WSTR, 3) DATEPART( "Millisecond", getdate() ),3)

This builds out the time component. I always advocate for using the System variable ContainerStartTime instead of GETDATE() as getdate is evaluated every time we access it while container start time is constant for the execution of a package. What I discovered and can't wait to blog about is the the expression language doesn't appear to get the milliseconds out of a ContainerStartTime but does work correctly for GETDATE() calls.

The final date variable then becomes something like

Date_Filter -> @[User::Date_YYYYMMDD] + " " + @[User::Date_HHMMSSms]

Which then makes my query usage look like

Query_Source -> "Select * from Table1 Where CreationDate > '" + @[User::Date_Filter] + "'"
billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I get what you are saying. But my prob is I am reading the value which already has YYYY-MM-DD hh:mi:Ss:mss. But I do see SSIS is reading it as mm/dd/yyyy MM:SS AM/PM. How do I handle this? – saurish das Oct 26 '20 at 19:03
  • It's your types in your SSIS variables. If it's a DT_DATE/DT_TIMESTAMP etc, localization rules are coming into play. Therefore, you need to explicitly provide the format you want and the type must be a DT_STR/DT_WSTR – billinkc Oct 26 '20 at 21:40
  • Thanks. I am trying out the solution you have provided. If it succeeds will update it here. – saurish das Oct 27 '20 at 04:49