Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

SSiS ---flat file output

Posted on 2014-11-21
8
Medium Priority
?
35 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 66

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 66

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

719 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