Solved

SSIS Flat Flie to SQL DB - Date Field

Posted on 2012-03-26
26
2,152 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
  • 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:huslayer
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
 
LVL 21

Expert Comment

by:huslayer
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:huslayer
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:huslayer
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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:huslayer
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:huslayer
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:
huslayer 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:huslayer
ID: 37779706
Thanks decoded, SJCFL-Admin, see you again in another SSIS question.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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

11 Experts available now in Live!

Get 1:1 Help Now