SSIS date with a value as '-' should be inserted as null

Question

Post reply

SSIS date with a value as '-' should be inserted as null

Aspg

Ten Centuries

Points: 1363
More actions
July 15, 2010 at 4:02 am

#141316

In an SSIS package I am using a Excel source with a date column, and some of the dates are hyphen . I want these hyphens to be treated as null when the data from the file is loaded into the database. when I execute the task I get the error "The value could not be converted because of a potential loss of data". How can I get the SSIS package to treat the hyphen date strings as nulls?
Aditi:-)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

gayathridevi.msit Ten Centuries Points: 1294 More actions · Answer 1

hi,

Try script component between excel source and database destination.

In script component use a function , if '-' then null.

insert the column into database as null value..

Regards,
Gayathri 🙂

Phil Parkin SSC Guru Points: 246956 More actions · Answer 2

gayathridevi.msit (7/15/2010)
hi,
Try script component between excel source and database destination.
In script component use a function , if '-' then null.
insert the column into database as null value..

That will do it, but a derived column is simpler and faster ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Aspg Ten Centuries Points: 1363 More actions · Answer 3

Thanks Phil ,

But i am Novice to SSIS ..Can you tell me how to do it in derived columns ..what about the other columns ..they also need conversion ..so can i use them all in Derived ...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

itsaditi2001 (7/15/2010)
But i am Novice to SSIS ..Can you tell me how to do it in derived columns ..what about the other columns ..they also need conversion ..so can i use them all in Derived ...

Use the FINDSTRING function in your derived column. If it finds a result, set null (see the NULL functions), otherwise take the original value. Use the (expression0) ? (result if true) : (result if false) syntax to achieve this.

You need to create a new expression for every column you want to convert.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Aspg Ten Centuries Points: 1363 More actions · Answer 5

I tried this ...

its not working for me..

TRIM([STARTD_CVV] ) == FINDSTRING( STARTD_CVV, "-", 1 ) ? (DT_STR, 8, 1252)NULL(DT_STR,8, 1252) :[STARTD_CVV]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

itsaditi2001 (7/15/2010)
I tried this ...
its not working for me..
TRIM([STARTD_CVV] ) == FINDSTRING( STARTD_CVV, "-", 1 ) ? (DT_STR, 8, 1252)NULL(DT_STR,8, 1252) :[STARTD_CVV]

Try the following:

(FINDSTRING(TRIM([STARTD_CVV]), "-",1) > 0) ? NULL(DT_STR, 8,1252) : [STARTD_CVV]

EDIT: for some reason, the site placed & gt; instead of a >

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Aspg Ten Centuries Points: 1363 More actions · Answer 7

Error at Data Flow Task [Derived Column [134]]: Attempt to parse the expression "FINDSTRING((TRIM(STARTD_CVV), "-",1) > 0 ) ? NULL(DT_STR, 8,1252) : STARTD_CVV" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

Cannot parse... what i m missing

Phil Parkin SSC Guru Points: 246956 More actions · Answer 8

You left out the square brackets around the field name ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Phil Parkin (7/15/2010)
You left out the square brackets around the field name ...

No, that is only necessary when the field name contains a space.

I think he forgot the initial (. (what is it called in English, parenthesis?)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246956 More actions · Answer 10

da-zero (7/15/2010)
Phil Parkin (7/15/2010)
You left out the square brackets around the field name ...
No, that is only necessary when the field name contains a space.
I think he forgot the initial (. (what is it called in English, parenthesis?)

Oh indeed. Well spotted.

'Parenthesis' is the fancy term, though I would refer to the initial '(' as the 'opening bracket'.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

itsaditi2001 (7/15/2010)
In an SSIS package I am using a Excel source with a date column, and some of the dates are hyphen . I want these hyphens to be treated as null when the data from the file is loaded into the database. when I execute the task I get the error "The value could not be converted because of a potential loss of data". How can I get the SSIS package to treat the hyphen date strings as nulls?
Aditi:-)

Don't load directly from Excel into a production table, use a staging table. Design the staging table to optimise the process of data capture from the source, thus eliminating errors like "The value could not be converted because of a potential loss of data". Add extra columns for processing if you wish.

Everything inside your database is valuable, everything outside is garbage. A staging table serves as a firewall between the two.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden