SolvedPrivate

SSiS ---flat file output

Posted on 2014-11-21
8
30 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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