SolvedPrivate

Using SQL script as data source in SSIS

Posted on 2013-11-07
7
23 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.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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