SSIS Where Clause w/ Date Functions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » SSIS Where Clause w/ Date Functions

SSIS Where Clause w/ Date Functions

Rate Topic

Display Mode

Topic Options

Author

Message

robshobs

Posted Tuesday, August 26, 2008 10:57 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:23 AM
Points: 41, Visits: 268

I am trying to get an SSIS package to work but am having problems with the WHERE clause. In the where clause, I need to do some date conversions and I keep getting errors. I've tried both SQL Server and Oracle functions and get errors both ways.

First, using a linked server, I can successfully execute the following query:

SELECT * FROM [PROJECTISO]..[PROJECTISO].[VU_ORDERS_TABLE]
WHERE convert(datetime,[LAST_UPDATED_DATE]) >= cast('08/25/2008' as datetime)

In SSIS, I am using the .NET Oracle connection manager. I program the Datareader Source with the following SQL:

SELECT * FROM VU_ORDERS_TABLE
WHERE convert(datetime,[LAST_UPDATED_DATE]) >= cast('08/25/2008' as datetime)

I am unable to save the source container. I get the error "An error occurred executing the provided SQL command." It should be noted that without the WHERE clause, the above query does work correctly.

So I changed the SQL to use Oracle functions. Now it reads:

SELECT * FROM VU_ORDERS_TABLE 
WHERE to_date(LAST_UPDATED_DATE) >= to_date('2008/08/25', 'YYYY/MM/DD')

I am able to save the source container but the SSIS package doesn't work. It returns the errors below. From what I can gather, the Oracle .NET driver wants Oracle SQL code but SSIS doesn't like the code.

Is there different SQL I should be using?
A different connection manager?

Your help is much appreciated.

Rob

ERROR from SSIS Package:
[Download vu_Orders_Table [1]] Error: The component "Download vu_Orders_Table" (1) was unable to process the data.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Download vu_Orders_Table" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Post #559052

Mazharuddin Ehsan

Posted Wednesday, August 27, 2008 2:46 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

Try Microsoft OLEDB Provider for Oracle

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #559428

robshobs

Posted Wednesday, August 27, 2008 5:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:23 AM
Points: 41, Visits: 268

I finally figured it out. It was the lack of a format mask on the date conversion. I changed

to_date(LAST_UPDATED_DATE)

to

to_date(LAST_UPDATED_DATE, 'YYYY/MM/DD HH24:MI:SS)

and now the code works.

Rob

Post #559514

Mazharuddin Ehsan

Posted Wednesday, August 27, 2008 6:33 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

Yes you are right. But it is surprising that LAST_UPDATED_DATE is not a Date field.

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #559561

robshobs

Posted Wednesday, August 27, 2008 7:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:23 AM
Points: 41, Visits: 268

Mazharuddin Ehsan (8/27/2008)

Yes you are right. But it is surprising that LAST_UPDATED_DATE is not a Date field.

I feed data from Oracle into SQL Server. When I started with SQL Server, the person training me told me to convert dates to text in the Oracle View that feeds the data. Then in SQL Server convert it back to a date. If we didn't do this, we found that we would sometimes get errors on the date field. So this is how I've done things for a couple of years.

About two months ago I found the REAL problem. SQL Server does not support dates prior to January 1, 1753 but Oracle does. If the date is prior to that date, the source container in SSIS would fail. By passing as a text and then converting, the conversion fails but SSIS can be programmed at that step to ignore the failure.

So if I program the where clause to pass only date fields on/after January 1, 1753, then the problem goes away. I've started doing this on new feeds but many old feeds still exist.

Of course the real problem is in the application. An ORDER_CREATE date cannot have happened 250 years ago! The application should not be allowing these dates but...

Rob

Post #559586

Mazharuddin Ehsan

Posted Wednesday, August 27, 2008 4:39 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

Of course the real problem is in the application. An ORDER_CREATE date cannot have happened 250 years ago! The application should not be allowing these dates but...

In your case it may be a bad data issue, but it was actually a deficiency in SQL server not to permit data earlier than 1/1/1753. I myself faced problems because of this while migrating date type data from Oracle to SQL Server. In my case, some data in Oracle was stored in Arabic calender (Today's date in Arabic is 25/07/1429)

Even though I tried to solve the issue by converting the Arabic (Hijri) dates to Gregorian as follows

select convert(datetime,'25/07/1429', 130)

2008-07-28 00:00:00.000

the proble still remained. Eventually I had to store the date data in string format in SQL Server for the problematic cases.

This deficiency is removed in SQL Server 2008 which can store date ranging from 1st January 0001 to 31st December 9999.

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #560009

PaulB-TheOneAndOnly

Posted Thursday, August 28, 2008 3:21 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 2,982, Visits: 4,393

Mazharuddin Ehsan (8/27/2008)This deficiency is removed in SQL Server 2008 which can store date ranging from 1st January 0001 to 31st December 9999.

Boomer!... how about my 154BC records comming from my twin servers in Nikaia and Antipolis?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #560229

Mazharuddin Ehsan

Posted Thursday, August 28, 2008 1:04 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:55 AM
Points: 378, Visits: 2,378

Boomer!... how about my 154BC records comming from my twin servers in Nikaia and Antipolis?

It would be interesting to know why it was required to store these prehistoric dates in an Oracle database and that too using the date fields. Normally, the benefit of having a date fileld is when calculations are involved. If no calculations are involved these date values can be safely stored as string.

Check the below link from the MS BOL on this issue for more info.
http://msdn.microsoft.com/en-us/library/ms151817.aspx

-----------------------------------------------------------
Time Is Money
Calculating the Number of Business Hours Passed since a Point of Time
Calculating the Number of Business Hours Passed Between Two Points of Time

Post #560757

« Prev Topic | Next Topic »

Permissions