[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2562
  • Last Modified:

SSIS Flat Flie to SQL DB - Date Field

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
decoded
Asked:
decoded
  • 9
  • 8
  • 8
2 Solutions
 
SJCFL-AdminCommented:
Either your formats are not lining up or the default date format does not match the format coming in on the flat file.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
decodedAuthor Commented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
decodedAuthor Commented:
My date column DOB looks like:

DOB
19500607
19870223

The destination column data type is:

"date"

and it is going to sql db
0
 
SJCFL-AdminCommented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
Alright...try that !

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

Open in new window

0
 
decodedAuthor Commented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
decodedAuthor Commented:
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
 
SJCFL-AdminCommented:
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
 
decodedAuthor Commented:
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
 
SJCFL-AdminCommented:
Is this the only field that will contain NULLS?
0
 
SJCFL-AdminCommented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
decodedAuthor Commented:
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
 
SJCFL-AdminCommented:
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
 
decodedAuthor Commented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
decodedAuthor Commented:
Flat Source Data Type: string[DT_STR]

Flat Source Data Type: string[DT_STR]
0
 
SJCFL-AdminCommented:
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
 
Jason Yousef, MSSr. BI DeveloperCommented:
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
 
SJCFL-AdminCommented:
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
 
decodedAuthor Commented:
opps i thought i did
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Thanks decoded, SJCFL-Admin, see you again in another SSIS question.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 9
  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now