SolvedPrivate

Using SQL script as data source in SSIS

Posted on 2013-11-07
7
17 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:codedigger
  • 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:codedigger
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:codedigger
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:codedigger
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:
codedigger 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:codedigger
ID: 39658852
Found it myself.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
query help 18 52
Sql to find top3 for each record 7 18
SQL server 2008 SP4 29 35
BULK LOGGED - log full 9 17
SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now