SolvedPrivate

Using SQL script as data source in SSIS

Posted on 2013-11-07
7
20 Views
Last Modified: 2016-02-11
Inside my SSIS package, which is based on a sql script, I'm encountering the error below,

" [ADO NET Source [1]] Error: The component "ADO NET Source" (1) was unable to process the data. ORA-01840: input value not long enough for date format "

The data source is numeric, and I'm converting it to date within the script, the map-to field datatype in the data-destination is "datetime", and here's my date conversion formula within my sql script;

TO_CHAR(to_DATE(MYDATE,'YYYYMMDD'),'MM/DD/YYYY HH24:MI:SS')AS LastModDate

Please advise.
0
Comment
Question by:Roberto Madro R.
  • 5
  • 2
7 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39631515
how do the MyDate values in your oracle database look like?

did you try doing the conversions using Derived column transformations?
0
 

Author Comment

by:Roberto Madro R.
ID: 39631540
The date in Oracle is "number" datatype, and the format is "20131010".

As for your second question, no, I'm not using Derived column but may be I should.
0
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39631843
Yeah try using Derived column instead... with an expression like below

Change MyDate to your Column Name and Convert the values to unicode or Non- unicode depending on your destination data type

(DT_STR,4,1252)(SUBSTRING(MyDate,1,4)) + "-"
  +  (DT_STR,4,1252)(SUBSTRING(MyDate,5,2)) + "-"
   + (DT_STR,4,1252)(SUBSTRING(MyDate,7,2))
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Roberto Madro R.
ID: 39643003
Danny;

I tried your solution but to no avail, here's the error I'm getting;

TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [Derived Column [408]]: The function "SUBSTRING" does not support the data type "DT_NUMERIC" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Error at Data Flow Task [Derived Column [408]]: Evaluating function "SUBSTRING" failed with error code 0xC0047089.

Error at Data Flow Task [Derived Column [408]]: Computing the expression "(DT_STR,4,1252)(SUBSTRING(MyDate,1,4)) + "-"  +  (DT_STR,4,1252)(SUBSTRING(MyDate,5,2)) + "-"  + (DT_STR,4,1252)(SUBSTRING(MyDate,7,2)) " failed with error code 0xC00470C5. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.

Error at Data Flow Task [Derived Column [408]]: The expression "(DT_STR,4,1252)(SUBSTRING(MyDate,1,4)) + "-"  +  (DT_STR,4,1252)(SUBSTRING(MyDate,5,2)) + "-"  + (DT_STR,4,1252)(SUBSTRING(MyDate,7,2)) " on "output column "Derived Column 4" (495)" is not valid.

Error at Data Flow Task [Derived Column [408]]: Failed to set property "Expression" on "output column "Derived Column 4" (495)".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
0
 

Author Comment

by:Roberto Madro R.
ID: 39646506
I went with derived column, and used the coversion formula you posted;

(DT_STR,4,1252)(SUBSTRING(MY_Date,1,4)) + "-" + (DT_STR,4,1252)(SUBSTRING(MyDate,5,2)) + "-" + (DT_STR,4,1252)(SUBSTRING(My_Date,7,2))

and I started getting this error;

[OLE DB Source [426]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E1D.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80040E1D  Description: "Requested conversion is not supported.".
0
 

Accepted Solution

by:
Roberto Madro R. earned 0 total points
ID: 39648992
The issue turned out to be in the Data Source Adapters, here's the link to the article;
http://support.microsoft.com/kb/244661
0
 

Author Closing Comment

by:Roberto Madro R.
ID: 39658852
Found it myself.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question