Go Premium for a chance to win a PS4. Enter to Win

x
?
SolvedPrivate

SSiS ---flat file output

Posted on 2014-11-21
8
Medium Priority
?
37 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 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

926 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