Solved

SSIS Flat Flie to SQL DB - Date Field

Posted on 2012-03-26
26
2,287 Views
Last Modified: 2012-08-14
Hello Experts,

I have stuck on a this little issue...i cant seem to import a date field YYYYMMDD from flat file Soucre to a SQL DB. I am using SSIS 2008.

I get the below error message:

SIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "dob" (1881)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "dob" (1881)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
0
Comment
Question by:decoded
[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
  • 9
  • 8
  • 8
26 Comments
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37768113
Either your formats are not lining up or the default date format does not match the format coming in on the flat file.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37768136
Hi,
Are you using any conversion?

Use a derived column and replace your column with ...the attached screen shot

That will work if your months and days has a leading zeros, let me know if your case is different

DT_DBTIMESTAMP) (Substring ([DOB],1,4)  + "-" +    Substring ([DOB],5,2)     + "-" +      right([DOB],2) )

Open in new window

derived.jpg
0
 

Author Comment

by:decoded
ID: 37768255
Should I change the the Data type of the DB destination? Right now it's "date"?

i made changes and i get the below erroe message:

ror: 0xC0049064 at Data Flow Task, Derived Column [3055]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [3055]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (3055)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "dob" (3439)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (3055) failed with error code 0xC0209029 while processing input "Derived Column Input" (3056). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37768797
what's your column DOB looks like? post sample dates ..
Also what's your ultimate destination data type ?

you could also cast to DATE

(DT_DBdate) (Substring ([DOB],1,4)  + "-" +    Substring ([DOB],5,2)     + "-" +      right([DOB],2) )
0
 

Author Comment

by:decoded
ID: 37771332
My date column DOB looks like:

DOB
19500607
19870223

The destination column data type is:

"date"

and it is going to sql db
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37771399
If you are using a delimited file, make sure that the delimiter is not appearing in any of your other text fields or it can throw off the alignment and SSIS would not be trying to parse the data that you believe it to be.  But in this case, it would also most likely not have thrown the error on the first record... So i did not want to continue to harp on this as a possible cause.  But advise testing with an input file of one perfect record first to eliminate all other variables and verifying on the the execution flow that the data is apssing as expected.  If needed, crerate a temporary file just prior to the database update of the records that exactly match the databse format. that way if it ever fails in the future, you will have a file to check immediately for exactly what is being passed to the update.  I like my database update step to be the simplest step in the process without any translations...
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37771494
Alright...try that !

(DT_DATE)(DT_DBDATE) (Substring ([DOB],1,4) + "-" + Substring ([DOB],5,2)  + "-" +      right([DOB],2) )

Open in new window

0
 

Author Comment

by:decoded
ID: 37771550
Sorry man Failed

Error mesage below:

Error: 0xC020902A at Data Flow Task, Derived Column [1748]: The "component "Derived Column" (1748)" failed because truncation occurred, and the truncation row disposition on "input column "dob" (1782)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (1748) failed with error code 0xC020902A while processing input "Derived Column Input" (1749). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1172]: The processing of file "testing.txt" has ended.

just so that you know there are some empty columns
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37771590
Hi,Did you read the error message, "truncation occurred", you need to adjust your source column size.

Also you can let the derived column create a new column not replace DOB.
Also to debug, add a data viewer and look at the data
0
 

Author Comment

by:decoded
ID: 37771653
Now i get this error message.

Error: 0xC0049064 at Data Flow Task, Derived Column [1748]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [1748]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (1748)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (2552)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

I looked at the data and its fine only thing i see is empty fields...would that make a difference on the expression.
de-column.JPG
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37771717
Yes. The system would need to know what you want it to do if there is no date on the input record.

Is there a default value you want the date set to or is the date to be set to the null value?

If a default value is desired, you could place that in the record prior to this logic and prevent the error.
0
 

Author Comment

by:decoded
ID: 37771740
I want to kept the null values.

Meaning if there is a NULL value in the source I want to import to destination NULL value as well.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37771822
Is this the only field that will contain NULLS?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37771951
huslayer may have to help correct my syntax here, but the way you are doing it I think you need this to handle the nulls for any fields that need to pass nulls through to the database...

SUBSTRING([DOB],1,1) == " " ? NULL(DT_DBdate) : (DT_DATE)(DT_DBDATE) (Substring ([DOB],1,4) + "-" + Substring ([DOB],5,2)  + "-" +      right([DOB],2) )
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37771953
Great, some progress here, try that.


isnull([DOB]) ? NULL(DT_DATE) :
(DT_DATE)(DT_DBDATE) (Substring ([DOB],1,4) + "-" + Substring ([DOB],5,2)  + "-" +      right([DOB],2) )

Open in new window

0
 

Author Comment

by:decoded
ID: 37772595
Different Error message guys,


Error: 0xC0049063 at Data Flow Task, Derived Column [1748]: The conditional operation failed.
Error: 0xC0209029 at Data Flow Task, Derived Column [1748]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (1748)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "Derived Column 1" (2552)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (1748) failed with error code 0xC0209029 while processing input "Derived Column Input" (1749). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.


i used the below expression:

isnull([dob]) ? NULL(DT_DATE) :(DT_DATE)(DT_DBDATE) (Substring ([dob],1,4) + "-" + Substring ([dob],5,2)  + "-" +      right([dob],2) )
cokumn.JPG
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37772634
ok.  Lets backtrack a bit.   add an output for failed records and make sure that it is only the null records that are failing.

(Go back to the code given you before the null logic was added.)

If only the null records are rejected, we are on the right track.  If all the records are rejected then you still have a layout problem you have not addressed.  But you need to confirm that good (non null) records are making it through ok...
0
 

Author Comment

by:decoded
ID: 37772775
Only the null records are failing.

i used the below expression:

(DT_DATE)(DT_DBDATE)(SUBSTRING(dob,1,4) + "-" + SUBSTRING(dob,5,2) + "-" + RIGHT(dob,2))
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37772802
I need to see the flat source connection, go to the advanced tab, and show the data type and info for that column please.
0
 

Author Comment

by:decoded
ID: 37772833
Flat Source Data Type: string[DT_STR]

Flat Source Data Type: string[DT_STR]
0
 
LVL 6

Assisted Solution

by:SJCFL-Admin
SJCFL-Admin earned 250 total points
ID: 37772935
Try this.  With fixed width fields, you wont get a null coming in. you will probably get 8 spaces.

SUBSTRING([DOB],1,8) == "        " ? NULL(DT_DBdate) : (DT_DATE)(DT_DBDATE) (Substring ([DOB],1,4) + "-" + Substring ([DOB],5,2)  + "-" +      right([DOB],2) )
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 250 total points
ID: 37772945
here you go..

LEN(dob) < 5 ? NULL(DT_DATE) : (DT_DATE)(DT_DBDATE)(SUBSTRING(dob,1,4) + "-" + SUBSTRING(dob,5,2) + "-" + RIGHT(dob,2))

Open in new window

0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37773391
You can split the credit :-) . huslayer provided the majority of the syntax for the translation, I only added in the test for spaces in a fixed format input....
0
 

Author Comment

by:decoded
ID: 37773456
opps i thought i did
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37779706
Thanks decoded, SJCFL-Admin, see you again in another SSIS question.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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