Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 32
  • Last Modified:

Using SQL script as data source in SSIS

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
Roberto Madro R.
Asked:
Roberto Madro R.
  • 5
  • 2
1 Solution
 
dannygonzalez09Commented:
how do the MyDate values in your oracle database look like?

did you try doing the conversions using Derived column transformations?
0
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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
 
dannygonzalez09Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Roberto Madro R.Programmer AnalystAuthor Commented:
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
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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
 
Roberto Madro R.Programmer AnalystAuthor Commented:
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
 
Roberto Madro R.Programmer AnalystAuthor Commented:
Found it myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now