SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Text qualifier question


SSIS Text qualifier question

Author
Message
Randy Doub
Randy Doub
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 778
I'm trying to replace an existing function with an SSIS package. Rows are exported to a comma delimited flat file for archiving and deleted from the source table.
The current method produces a file with data quoted and blank columns not quoted.
Ex "data1","data2",,"data4"
I set the Text Qualifier on my Flat File Connection Mgr to "
But I get this: "data1","data2","","data4"
All columns in the Flat File Connection Mgr are strings.
I don't normally quote text on an export, but since I'm mimicking another process I thought I'd try and be exact.
Can this be done?
Thanks all.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8627 Visits: 7660
I believe you can use a derived column to null the field before it goes outbound. You've got a 0-length in there and that's what's trying to send, if my faulty memory serves me correctly.

Basically something like this:
TRIM(cola) == "" ? (DT_STR,<len>,1252)NULL(DT_STR,<len>,1252) : cola


- 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
Randy Doub
Randy Doub
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 778
I couldn't get that to work. I mean the syntax is good, but I got the same result. Its true that these are 0-length strings. So to make them NULL I changed the select query to:
case when ltrim(rtrim(mycol)) = '' then NULL else ltrim(rtrim(mycol)) end as mycol.
In the preview I saw the NULL value, but it still got quoted. Seems like the Text Qualifier in the flat file connection manager is overriding everything.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8627 Visits: 7660
Hm. The couple of semi-official things I've seen seem to point to that functioning. Very strange.

Can you check the dataviewer right before the outbound flat file and make sure that the columns you have mapped are actually null?


- 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
Randy Doub
Randy Doub
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 778
Yea, they are empty string before the derived column transformation
and NULL after the derived column transformation.

And the result is all columns quoted.

Screen shots are attached.

In the attached gifs you can see column EdiCarrier is one of the culprits. This columns has data in some rows, but not all. So I changed the query to only select rows where EdiCarrier = '' wondering if the column was NULL for every row, might it make a difference. There was no change in the behavior. I also deleted the flat file connection mgr and recreated it. I've seen it where it seems like a flat file connection definition gets "stuck" after making edits to it.
Attachments
viewer_pre.gif (12 views, 34.00 KB)
viewer_post.gif (13 views, 35.00 KB)
result.gif (16 views, 79.00 KB)
Randy Doub
Randy Doub
Old Hand
Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)Old Hand (388 reputation)

Group: General Forum Members
Points: 388 Visits: 778
I read elsewhere that since a flat file is a text file, if you use a Text Qualifier it will be applied to every column.
So here's what works... remove the Text Qualifier and as a derived column use:
mycol !="" ? "\"" + mycol + "\"" : mycol
This adds two to the length of every column with data. I have some datetime data types in the table that got converted to char(10) for formatting in the select query and I was failing with a truncation error on them. I could not edit the length in the Derived Column Transformation Editor.
So I tried mycol !="" ? DT_STR(<len+2>,1252)("\"" + mycol + "\"") : mycol. It didn't fail, but gave me "10/09/201 in the date columns in the output file. Not sure what I was doing wrong there.
So I changed the select query to pad all the columns giving ample space for the max data plus the quotes. Ex: convert(varchar(50),convert(varchar(10),datetimeColumn,101)) as mycol.
Learned a little more about SSIS Expression Language today!
Thanks for all your time and effort.
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4146 Visits: 7865
Randy Doub (2/20/2013)
I read elsewhere that since a flat file is a text file, if you use a Text Qualifier it will be applied to every column.
So here's what works... remove the Text Qualifier and as a derived column use:
mycol !="" ? "\"" + mycol + "\"" : mycol
This adds two to the length of every column with data. I have some datetime data types in the table that got converted to char(10) for formatting in the select query and I was failing with a truncation error on them. I could not edit the length in the Derived Column Transformation Editor.
So I tried mycol !="" ? DT_STR(<len+2>,1252)("\"" + mycol + "\"") : mycol. It didn't fail, but gave me "10/09/201 in the date columns in the output file. Not sure what I was doing wrong there.
So I changed the select query to pad all the columns giving ample space for the max data plus the quotes. Ex: convert(varchar(50),convert(varchar(10),datetimeColumn,101)) as mycol.
Learned a little more about SSIS Expression Language today!
Thanks for all your time and effort.


Randy, just watch out for when mycol contains any double quotes, then you have a problem...

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search