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

SSIS Export to Flat File Question Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 8:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:20 AM
Points: 43, Visits: 454
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!!
Post #1474639
Posted Wednesday, July 17, 2013 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 7:48 AM
Points: 32, Visits: 290
Redacted as this will not solve OP's problem.
Post #1474651
Posted Wednesday, July 17, 2013 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:20 AM
Points: 43, Visits: 454
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!
Post #1474661
Posted Wednesday, July 17, 2013 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:20 AM
Points: 43, Visits: 454
Not sure if i am doing this correct but i get an error as i followed your advice. I attached a screenshot.



  Post Attachments 
Doc2.docx (1 view, 56.13 KB)
Post #1474668
Posted Wednesday, July 17, 2013 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:20 AM
Points: 43, Visits: 454
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.
Post #1474669
Posted Wednesday, July 17, 2013 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 7:48 AM
Points: 32, Visits: 290
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.
Post #1474671
Posted Wednesday, July 17, 2013 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:20 AM
Points: 43, Visits: 454
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!
Post #1474677
Posted Wednesday, July 17, 2013 7:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1474850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse