SSIS Export to Flat File Question

Question

Post reply

SSIS Export to Flat File Question

Keejli69

Ten Centuries

Points: 1307
More actions
July 17, 2013 at 8:17 am

#280184

Hello All, I am a noobie. I am using SSIS 2008 R2 to export data from sql server 2008 R2 to a flat file. My question is how can i add a pipe delimiter to the end of the header column? I tried to add the pipe delimiter to the alias but it doesn't like it in SSIS. Right now i am able to get the output like this:
FirstName| LastName| Email
Jon| Doe| jd@email.com|
Mike| Jones| mj@email.com|
I need the output to be like this:
FirstName| LastName| Email |
Jon| Doe| jd@email.com|
Mike| Jones| mj@email.com|
Any help would be greatly appreciated. Thanks!!:-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sdevanny Say Hey Kid Points: 698 More actions · Answer 1

sdevanny

Say Hey Kid

Points: 698

July 17, 2013 at 8:33 am

#1633316

Redacted as this will not solve OP's problem.

Keejli69 Ten Centuries Points: 1307 More actions · Answer 2

sdevanny (7/17/2013)
How did you add the | to the individual records? If that is easily removable you might try adding a Derived Column Transform and add a new column named (literally) | and make the expression "|".
This will add a column named | with a value of "|" to your data flow - just make sure it is the last column and you should be ok.
I am curious though, why do you need a column delimiter at the end of your record when there are no more columns in the record?
Thanks - hope this helped!

I added the pipe in the sql script like this below:

ADDRESS_MASTER.ADDR_LINE_1 + '|' AS 'email'

The vendor that is requesting this file would like a pipe delimiter at the end to close out that row of data.

Thanks!

Keejli69 Ten Centuries Points: 1307 More actions · Answer 3

Not sure if i am doing this correct but i get an error as i followed your advice. I attached a screenshot.

Keejli69 Ten Centuries Points: 1307 More actions · Answer 4

elee1969 (7/17/2013)
Not sure if i am doing this correct but i get an error as i followed your advice. I attached a screenshot.

i figured it out. I forgot to add the quotation marks.

sdevanny Say Hey Kid Points: 698 More actions · Answer 5

If you used my suggestion with a dervied column, you might want to double check and make sure that you have a correct count of delimiters and columns now. I believe that my suggestion will actually add one extra delimiter - sorry I missed that when originally replied.

Keejli69 Ten Centuries Points: 1307 More actions · Answer 6

sdevanny (7/17/2013)
If you used my suggestion with a dervied column, you might want to double check and make sure that you have a correct count of delimiters and columns now. I believe that my suggestion will actually add one extra delimiter - sorry I missed that when originally replied.

i checked and you are correct. I get this now:

partnerkey|first_name|middle_name|last_name|email||

12345|David|jones|jones |jones@email.com||

How do i fix this?

Thanks!

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 7

elee1969 (7/17/2013)
sdevanny (7/17/2013)
If you used my suggestion with a dervied column, you might want to double check and make sure that you have a correct count of delimiters and columns now. I believe that my suggestion will actually add one extra delimiter - sorry I missed that when originally replied.
i checked and you are correct. I get this now:
partnerkey|first_name|middle_name|last_name|email||
12345|David|jones|jones |jones@email.com||
How do i fix this?
Thanks!

Any of these methods will do the job...

DECLARE @STR AS VARCHAR(500)

SET @STR = 'partnerkey|first_name|middle_name|last_name|email||'

SET @STR = REPLACE(@str,'||','|')

SELECT @STR

SET @STR = 'partnerkey|first_name|middle_name|last_name|email||'

SET @STR = LEFT(@str,LEN(@str)-1)

SELECT @STR

SET @STR = 'partnerkey|first_name|middle_name|last_name|email||'

SET @STR = REVERSE(STUFF(REVERSE(@str),1,1,''))

SELECT @STR