<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
SSIS Text qualifier question
SSIS Text qualifier question
Rate Topic
Display Mode
Topic Options
Author
Message
Randy Doub
Randy Doub
Posted Tuesday, February 19, 2013 2:14 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 132,
Visits: 600
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.
Post #1421848
Evil Kraig F
Evil Kraig F
Posted Wednesday, February 20, 2013 1:33 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 5,674,
Visits: 6,117
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
Post #1421971
Randy Doub
Randy Doub
Posted Wednesday, February 20, 2013 9:58 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 132,
Visits: 600
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.
Post #1422167
Evil Kraig F
Evil Kraig F
Posted Wednesday, February 20, 2013 10:42 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 5,674,
Visits: 6,117
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
Post #1422206
Randy Doub
Randy Doub
Posted Wednesday, February 20, 2013 12:25 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 132,
Visits: 600
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.
Post Attachments
viewer_pre.gif
(
2 views,
34.61 KB
)
viewer_post.gif
(
0 views,
35.16 KB
)
result.gif
(
0 views,
79.29 KB
)
Post #1422243
Randy Doub
Randy Doub
Posted Wednesday, February 20, 2013 2:51 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 132,
Visits: 600
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.
Post #1422320
mister.magoo
mister.magoo
Posted Wednesday, February 20, 2013 5:05 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,290,
Visits: 3,860
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
Post #1422361
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
