Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 28
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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