Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SeekingWisdom
Starting Member
24 Posts |
 Posted - 2008-05-08 : 17:11:39
|
 Greetings, I am attempting to create a flat file delimited by |. I am using (ISNULL(LIN1_OPT_ADDR) ? "" : LIN1_OPT_ADDR + "| ") to replace the blank address column with the pipe delimiter. So that a row that would consist of: Customer Number,Name,Address Line1,City,State 12345,ACE HARDWARE INC. ,801 Rockefeller St.,New York, New York 56789,BUILDING SUPPLY INC., ,Wichita, Kansas Should end up as: 12345|ACE HARDWARE INC.|801 Rockefeller St.|NEW YORK|NEW YORK 56789|BUILDING SUPPLY INC.||Wichita|Kansas When I run the data flow to create the flat file the file contains the following: 12345|ACE HARDWARE INC.|801 Rockefeller St.|NEW YORK|NEW YORK 56789|BUILDING SUPPLY INC.| | |Wichita|Kansas Can anyone tell me what I am doing wrong?  Thanks. |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2008-05-08 : 20:36:46
|
| It looks like you've got a blank string rather than a NULL value in your LIN1_OPT_ADDR field. To get the output you indicated, use the following:(ISNULL(LIN1_OPT_ADDR) || LEN(TRIM(LIN1_OPT_ADDR)) ? "" : LIN1_OPT_ADDR + "| ") This will test for a NULL value or an empty string.*** However ***If you are sending this to another transformation or destination, I suspect that you will want the additional pipe in there. If, for example, you are sending the output to a SQL Server table, the rows without a LIN1_OPT_ADDR value will have one fewer columns than those with a value in that field, and you'll end up with the City value where the LIN1_OPT_ADDR is expected.Post back here if this doesn't answer your question and I'll try to help.hth,Tim---------------------Tim Mitchellwww.BucketOfBits.com |
 |
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2008-05-09 : 08:34:28
|
| Tim thank you for your reply. I have placed the sample code in the expression as you present it. Unfortunately it turns red and when I place the cursor over it I am presented with the following: TITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Derived Column [3124]]: Attempt to set the result type of binary operation "ISNULL(LIN1_OPT_ADDR) || LEN(TRIM(LIN1_OPT_ADDR))" failed with error code 0xC0047080.Error at Data Flow Task [Derived Column [3124]]: Computing the expression "(ISNULL(LIN1_OPT_ADDR) || LEN(TRIM(LIN1_OPT_ADDR)) ? "" : LIN1_OPT_ADDR + "|")" failed with error code 0xC0047084. The expression may have errors, such as divide by zero, that cannot be detected at parse time, or there may be an out-of-memory error.Error at Data Flow Task [Derived Column [3124]]: The expression "(ISNULL(LIN1_OPT_ADDR) || LEN(TRIM(LIN1_OPT_ADDR)) ? "" : LIN1_OPT_ADDR + "|")" on "output column "NEW_ADDR1" (3143)" is not valid.Error at Data Flow Task [Derived Column [3124]]: Failed to set property "Expression" on "output column "NEW_ADDR1" (3143)".------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)Any suggestions or ideas? Thanks, Carlos |
|
|
|
SeekingWisdom
Starting Member
24 Posts |
Posted - 2008-05-09 : 08:39:24
|
| Tim, additionally the output of this process is to generate a pipe delimited flat file. In the case where the address is blank, I should provide a pipe that would account for or indicate an empty column. Any additional thoughts on this need. Help Please! Again, I am grateful for your response. Carlos |
|
|
|
|
|
|
|
|
Derived Column Transformation Editor