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
Solved

Columns in delimited file may vary when input to SSIS

Posted on 2007-11-16
4
937 Views
Last Modified: 2012-06-21
Hi everyone.

A delimited file is being sent to us from another company. The file is supposed to have 10 columns in each row. We are going to process the file using SSIS (2005).

Question - how do we handle the file if some of the rows are bad - are missing one or more columns?

If my package reads the file using a flat file source, when I run the package with a file where some of the rows have fewer than the expected 10 columns, my package abends on the flat file source task.

All we can think of doing is writing .net code to process the file as the first step of the package or even outside the package, to remove bad rows from the file before it hits SSIS.  

Thank you.
0
Comment
Question by:glen_esq
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 20302570
not a great solution but here goes

Rather than writing your own, download gawk.exe (google for it) to search for rows with 10 commas in it and redirect the output to a good input file, then load that file.

Or bulk insert each row into a single varchar column table.

bulk insert mytable
from 'c:\path\file.csv'

create view v_my_good_table
select * from mytable where len(replace(dta,',')) = len(dta) - 10

exec master..xp_cmdshell "select * from v_my_good_table" queryout "goodfile.csv" -S server -U user -P pwd '

Then load goodfile.csv using your package

Or -- just parse the single column data using charindex and substring and load the columns yourself for rows that are correct



0
 
LVL 3

Expert Comment

by:yellowjetski
ID: 20306080
You can try to use the Exception Row feature in SSIS to handle the bad rows.

Overview: SSIS will read each row assuming it qualifies to the pre-defined columns. When there's a bad row of any reason (incorrect number of columns, invalid data type, etc.) You can have SSIS write the bad row only to a exception file and the package continues to process the next line without failing the whole package.

Let me know if you have any questions regarding my solution.
0
 

Author Comment

by:glen_esq
ID: 20312931
Thank you both very much for your responses.

yellowjetski - more info please on your solution. I have been testing with a very simple package. A flat file data source which reads my delimited file, and on its failure precedence constraint, I have attached a flat file destination.

If I remove the column delimiter (in our case a tilde '~') and the corresponding data, in other words, my input file has only 9 columns instead of 10, the flat file source bombs, the package proceeds no further - the tlat file source turns red, that's all she wrote.

Can you give me more info on the Exception Row feature? To which type of Control Flow or Data Flow task are you referring?
0
 

Author Comment

by:glen_esq
ID: 20313074
Should have added more info - If I set both Error and Truncation error conditions to redirect row, my package will not fail (perhaps this is the Exception Row you are referring to), however, SSIS doesn't handle this as well as I'd like.

I have 8 rows of input, the 6th row I removed one column and its associated delimiter leaving it with 6 columns. I have data viewers on the success and failure constraints from my flat file source. Rows 1 to 5 and 8 are processed successfully, and rows 6 and 7 go to the flat file destination specified on the failure constraint of my flat file source. I'm losing the 6th row (which is missing a column) and the 7th row which contains good data, but SSIS grabs anyway). I don't want to lose the 7th row in these situations.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

765 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