July 15, 2010 at 4:02 am
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:-)
July 15, 2010 at 4:25 am
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 🙂
July 15, 2010 at 4:29 am
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 ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 15, 2010 at 4:57 am
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 ...
July 15, 2010 at 5:00 am
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
July 15, 2010 at 5:31 am
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]
July 15, 2010 at 5:52 am
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
July 15, 2010 at 6:19 am
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
July 15, 2010 at 6:26 am
You left out the square brackets around the field name ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 15, 2010 at 6:45 am
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
July 15, 2010 at 6:51 am
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'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 15, 2010 at 6:53 am
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.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply