Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue with Derived Column Expression SSIS-Help!! Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 5:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:03 PM
Points: 136, Visits: 1,227
Hi,

I have an issue with an expression that i am using in the Derived Column transoformation in SSIS.

It is supposed to find True/False values from the column (ColumnX) from an excel sheet. And it is usppoed to return 1 for False and ) for True. However, the output of the transofrmation is always 0 in the Destination table.

The expression i am using is

(DT_I2)(["ColumnX"] == "TRUE" ? 1 : 0)

The output is always equating to 0 regardless of whether there is a FALSE or TRUE in the values of the column from the excel sheet.

Could someone help me find what the issue is?



Post #1524840
Posted Thursday, December 19, 2013 5:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
Double click on the stream between the excel source and the derived column. First, check the metadata on ColumnX and make sure it's what you expect (in this case, DT_STR or DT_WSTR).

Next, if everything there seems to be lining up, Pop a Data Viewer up between the two items and make sure you move ColumnX into the viewer. Make sure the OLEDB component bringing the data into SSIS is giving you what you actually expected.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1524845
Posted Friday, December 20, 2013 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:03 PM
Points: 136, Visits: 1,227
Yes, i checked and and the metadata for column X is DT_STR.

I'm using a Flat File Source component to bring the data in from excel. When i put the Data viewer, i noticed that it is bringing in the column values as "False" and "True" as opposed to "TRUE" and "FALSE" , the latter which i'm using to check in my expression.

Could that be the reason?

However, when i tried updating the expression to use "True" , it still returns the same data.



Post #1525029
Posted Friday, December 20, 2013 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:03 PM
Points: 136, Visits: 1,227
Also wanted to add, the source is a .CSV file-not excel.
Post #1525152
Posted Friday, December 20, 2013 2:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:03 PM
Points: 136, Visits: 1,227
Fixed the issue- Had to modify the expression to

(DT_I2)(["ColumnX"] == "True" ? 1 : 0)

Post #1525180
Posted Friday, December 20, 2013 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
Sorry, took a bit to get back here for me.

Yeah, SSIS is case sensitive. You'll find the same in LOOKUP component too. UPPER() is your friend there.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1525190
Posted Friday, December 20, 2013 3:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
Ignore, wrong thread.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1525201
Posted Tuesday, December 24, 2013 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:03 PM
Points: 136, Visits: 1,227
It works if i run the Data flow component in isolation. However, when the package is run through a scheduled job each night, the issue persists. Very strange.

The only control flow item that precedes it , is the unziping of the folder and copying of the .csv files to the destination folder. After that the Data flow components execute to bring the data from the .csv file into a sql table.

I made the change(to the expression) to the package directly and saved it to the file system. Not sure why it would work independently and not part of the package?
Post #1525776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse