August 26, 2008 at 10:57 am
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.
August 27, 2008 at 2:46 am
Try Microsoft OLEDB Provider for Oracle
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 27, 2008 at 5:32 am
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
August 27, 2008 at 6:33 am
Yes you are right. But it is surprising that LAST_UPDATED_DATE is not a Date field.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 27, 2008 at 7:01 am
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
August 27, 2008 at 4:39 pm
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.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
August 28, 2008 at 3:21 am
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.August 28, 2008 at 1:04 pm
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
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply