Roberto Madro R.
asked on
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,'YY YYMMDD'),' MM/DD/YYYY HH24:MI:SS')AS LastModDate
Please advise.
" [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,'YY
Please advise.
ASKER
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.
As for your second question, no, I'm not using Derived column but may be I should.
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 ))
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(
+ (DT_STR,4,1252)(SUBSTRING(
+ (DT_STR,4,1252)(SUBSTRING(
ASKER
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.DTSPi pelineWrap )
-------------------------- ----
BUTTONS:
OK
-------------------------- ----
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
Error at Data Flow Task [Derived Column [408]]: The expression "(DT_STR,4,1252)(SUBSTRING
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.DTSPi
--------------------------
BUTTONS:
OK
--------------------------
ASKER
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.".
(DT_STR,4,1252)(SUBSTRING(
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.".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found it myself.
did you try doing the conversions using Derived column transformations?