SSIS Variable Date Failing between SQL Server and ORACLE

Question

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

What format is BUSINESS_DATE field stored as in the Oracle DB — rs.
– rs., Commented Sep 20, 2018 at 14:01
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
– Hilarion, Commented Sep 20, 2018 at 16:01
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
– D Smith, Commented Sep 21, 2018 at 5:11

D Smith · Accepted Answer · 2018-09-21 06:45:02Z

0

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!

answered Sep 21, 2018 at 6:45

D Smith

1271 gold badge3 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

skunk808 · Accepted Answer · 2018-10-13 03:05:28Z

0

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://learn.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.

edited Oct 13, 2018 at 3:05

answered Sep 20, 2018 at 20:49

skunk808

12 bronze badges

2 Comments

D Smith Over a year ago

Hi Paul, I have attempted this yesterday however I get the following error when I click on the Parameters button: "Provider cannot derive parameter information and SetParameterInfo has not been called." I am using the OLE DB Source - Any ideas?

skunk808 Over a year ago

Hmm, my bad. It seems query parameters function is not working with that provider.

Collectives™ on Stack Overflow

SSIS Variable Date Failing between SQL Server and ORACLE

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related