Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Columns in delimited file may vary when input to SSIS

Posted on 2007-11-16
4
Medium Priority
?
942 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
[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
  • 2
4 Comments
 
LVL 12

Accepted Solution

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

670 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