SSIS Variable Date Failing between SQL Server and ORACLE

0
votes

Good Afternoon All, I have spent about 6 hours trying to get formatting to work through SSIS using a Max Date Variable to identify into a where clause - Just have no luck!

I have created a variable called my_date which fetched the Max(Date) from a local SQL server table to understand the last load point for that table - using the below code:

SELECT        CAST(FORMAT(MAX(Business_Date), 'dd-MMM-yyyy') AS varchar) AS my_date FROM Table

This fetches the date correctly as 17-Sep-2018.

I have then mapped my result set as my_date -> User::max_date

I have set my max_date variable to a string data type under the package scope.

I have tested my variable out by using breakpoints to ensure this runs all the way through in the correct format - and this works 100%.

I then have a data flow task running to fetch data from my ORACLE DB to insert into my SQL Server table which contains the following SQL command:

SELECT *
FROM            Table2
WHERE        (BUSINESS_DATE > to_date('@[User::max_date]', 'DD-MON-YYYY'))

However I get the ORA-01858 - [TABLE] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01858: a non-numeric character was found where a numeric was expected ".

If I go and replace my variable directly with the contents of the variable shown by the breakpoint in locales it works perfectly!

I have attempted multiple format types from the initial export through to the final where clause and this seems to be the closest I have come to pushing it through but it is still complaining about format.

Please help - Images below to help see setup.

Control Flow - displaying the execute SQL and the data flow task

Locale showing Variable is inserting after breakpoint is reached

2
sqloraclessis
What format is BUSINESS_DATE field stored as in the Oracle DB - rs.
It is stored as DD-MON-YYYY so basically 17-SEP-2018 - D Smith
The question is, what type is used for the BUSINESS_DATE column? Is that VARCHAR2, DATE, TIMESTAMP (with or without TIMEZONE), etc. Additionally: Excuse my ignorance on SSIS part, but how is "User:max_date" defined? What type of entity is this? If it's something datetime-like, then maybe what gets substituted into the query, is not the text you put in there. I.e. the text got parsed into a date and now, when pushed to the query, is then converted to a string of a different format? Also does SSIS not provide a way to use parameter binding, instead of substitution? - Hilarion
Good Morning, Apologies :) so basically it works as follows: 1. SQL Server Business Date (Source of parameter and final insert source) DATETIME 2. ORACLE Business Date (Source of data migration) DATE - however every date in ORACLE is automatically a datetime . The variable is set to a string as I cast the business date from sql server into a VARCHAR the reformat it back to a date with the to_date in ORACLE. I have attempted running it as DATETIME across all three - no luck. I am going to test the parameters out now and see if it works - hopefully!!! - D Smith

2 Answers

0
votes

Managed to get it working!

By adding an intermediary variable where the value and expression contains the following:

"SELECT        *
FROM            TABLE
WHERE        (BUSINESS_DATE > to_date('"+@[User::max_date]+"' , 'DD-MON-YYYY'))"

I then amended my source OLEDB to SQL Command from variable and selected the above variable created and it worked perfectly!

0
votes

Try mapping your user parameter in the "OLE DB Data Source Editor", under "Parameters".

1) Change SQL Command Text (change @[User::max_date] to ?), like this:

SELECT *
FROM            Table2
WHERE        (BUSINESS_DATE > to_date('?', 'DD-MON-YYYY'))

2) Then in the parameter editor, map parameter 1 to @[User::max_date]. https://docs.microsoft.com/en-us/sql/integration-services/data-flow/map-query-parameters-to-variables-in-a-data-flow-component?view=sql-server-2017

Also, the "Oracle Provider for OLE DB" behaves differently than the "Microsoft OLE DB Provider for Oracle", so it depends which you are using.