Hello frnds i am getting these errors in the SSIS package which is importing data from oracle to SQL SERVER. 1)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format". 2)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (35)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (35)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. 3)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (22) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (35). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. 4)[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. 5)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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. Is it due to some date format? Thanks in advance.. dimrd_SQL

you might need to use To_Date function to convert into date time, see folllowing, http://techonthenet.com/oracle/functions/to_date.php i can be more helpful if you provide your query.Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

tHANKS BUT I USED THE SAME PACKAGE FOR OTHER TABLES. IT WORKED BUT I DID NOT GET THE LINK ABOVE WHERE SHOULD I USE THE TO_DATE FUNCTION. WOULD YOU ELABORATE IT PLS? tAHNKSdimrd_SQL

Hi rd_2718, Gaurav shoule mean that using To_Date function in the Oracle SQL query statement to convert the value to a valid datetime datatype. In SQL Server Integration Services(SSIS), if the string(DateTime column) is almost in the right format, we just need to rearrange the date parts into YYYY-MM-DD format . Use SUBSTRING to reformat it and cast it, using a Derived Column component. An expression like this should do the trick: (DT_DBTIMESTAMP)(SUBSTRING([Column], 7, 4) + "-" + SUBSTRING([Column], 1, 2) + "-" + SUBSTRING([Column], 4, 2) + SUBSTRING([Column], 11, 13)) For more information on converting strings to datetimes, please see the MSDN page: Integration Services Data Types: http://msdn.microsoft.com/en-us/library/ms141036.aspx Or please see Todd's blog: http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

jin Thanks I have chosen now derived column tranfermation for the tables which were giving problem columns which appear there are derived column name, derived column, expression etc. I am using SSIS very first time. So what i am doing is just picking the substring string function and placing into the expression column. Then iam also choosing the derived column. After this What should i write in character expression, Start and length. I am clue less Thanksdimrd_SQL

Hi rd_2718, I noticed you have opened a new thread for this issue, and Todd is going to help you. http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/749d8a33-d930-4b7b-b383-322236a5e77e Thanks, Jin ChenJin Chen - MSFT