SolvedPrivate

SSiS ---flat file output

Posted on 2014-11-21
8
29 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

773 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