SolvedPrivate

SSiS ---flat file output

Posted on 2014-11-21
8
32 Views
Last Modified: 2016-02-10
In SSIS I am using OLE DB Source to select data from a database to flat file destination
The output has commas and I am trying to get rid of the commas.
eg

Current output

John,   Smith, , 4503 oak Lane,

I want the output as  

John   Smith  4503 oak Lane
0
Comment
Question by:Nana Hemaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40458526
If you mean remove the commas in the source data:
   (1)  In your OLEDB Source create a view or stored procedure where the offending columns have the commas weeded out via REPLACE(column_name, ',', '').
   (2)  (Harder)  In SSIS between source and destinations use derived column tasks to also weed out commas.

If you mean remove the commas that separate the columns:
  (1)  Open up the flat file connection that is your destination, and choose a delimeter other than comma.
0
 

Author Comment

by:Nana Hemaa
ID: 40458568
--If you mean remove the commas that separate the columns:
   --(1)  Open up the flat file connection that is your destination, and choose a delimeter other than comma

I have changed the delimiter from comma to other options but the output is the same and I am not sure why
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40458577
>but the output is the same and I am not sure why
The only possibilities I can think of are that there are commas in your data, or there are NULL / empty values '' in your data which makes the comma delimeters appear side-by-side.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Nana Hemaa
ID: 40458583
Yes there are spaces in between some values and they have to stay that way(it is a requirement) Is there a way around this
John,   Smith,  4503 oak Lane,

 I want the output as  

 John   Smith  4503 oak Lane
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40458593
In the example above, is this one column, or three columns (first name, last name, address)?
0
 

Author Comment

by:Nana Hemaa
ID: 40458599
three  columns with spaces in between
like
select  firstname, replicate ('  ', 1), Lastname, replicate ('  ', 2), address and so on
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40458614
Alrighty then.  Use REPLACE() on all three coluimns.
-- To illustrate
Declare @val varchar(100) = 'yabba, dabba, doo'

-- Looky here, no commas
SELECT REPLACE(@val, ',', '') 

select 
   REPLACE(firstname, ',', '') + ' '
   REPLACE(Lastname, ',', '')+ ' '
   -- address and so on

Open in new window

0
 

Author Comment

by:Nana Hemaa
ID: 40463165
Thanks Jim...   I made a typo in my question.   I meant to write
 Insert   firstname, replicate ('  ', 1), Lastname, replicate ('  ', 2), address and so on  instead of select.   A customer table is created this way with some of the columns filled with spaces of specific length.  In the SSIS package I select * from the customertable into a flat file  destination  -- file. I tried manipulating the flat file properties to get rid of the commas but did not work

So the only way will be to select  the columns individually with replace right?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question