SSIS Flat File Import - CSV Files with empty information

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS Flat File Import - CSV Files with empty...

SSIS Flat File Import - CSV Files with empty information

Rate Topic

Display Mode

Topic Options

Author

Message

Tava

Posted Tuesday, October 09, 2012 8:49 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 5:50 PM
Points: 76, Visits: 299

Hi All, Hopefully I can get a bit of help on the below.

Firstly a bit of background I have a SSIS package which I've developed & runs perfectly fine,I simply pass a CSV through the Flat File Connection Manager which is automated from our FTP & as mentioned it works as expected however; some files we get from our supplier have a blank line in it which still gets inserted into the database obviously with incorrect information.

The file is Comma Separated, and also with a text qualifer of " and format being "Delimited"

Example of the file is setup like this.

Sample File 1: - BAD

Description: Recieve 1 Supplier Code and 1 blank line

Date, Code, Name, value
5/10/2012, TEL, Telstar, 52
, , , ,

Sample File 2: - BAD

Description: Recieve No Supplier Code but just 1 blank line

Date, Code, Name, value
, , , ,

Sample File 3: - GOOD

Description: Recieve 1 Supplier Code this time no blank line.
Date, Code, Name, value
6/10/2012, PLS, Please, 11

How do i just ignore any lines if there blank , , , , ? i still want to import the good line.

Post #1370655

Tava

Posted Tuesday, October 09, 2012 10:53 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 5:50 PM
Points: 76, Visits: 299

Hi all,

I resolved the issue by doing a additional check in my stored proc, checking to see if a certain value <> ''

The value should never be empty as it is a required field or it can't be inserted anyway.since the supplier file only has 3-5 rows a day there isn't any performance issue so the process is fine.

However in saying that I am still interested if there is a way to perform this through the SSIS package -

Post #1370671

sqlusers

Posted Wednesday, October 10, 2012 12:24 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 196, Visits: 468

1. You can make use of String Funtions in Derived Transformation to make change as NULL.

2. Then use Row Count Transformation to get the count of empty or null.

3. If the Row Count is greater than one, then don't load it.

Please try. This may help you! Smile

Post #1370702

Phil Parkin

Posted Wednesday, October 10, 2012 12:51 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 4,324, Visits: 9,665

I'd do this by using a Conditional Split in the dataflow. Just redirect the unwanted rows to an output which goes nowhere, while letting the good rows run through as usual.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1370715

Tava

Posted Wednesday, October 10, 2012 3:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 10, 2013 5:50 PM
Points: 76, Visits: 299

Thanks for the replies, I tried both those ways (derived & transformation) prior to doing my idea and I was not able to get it to work. I was on the right track which is a positive probably just need to understand the process more

Post #1370775

« Prev Topic | Next Topic »

Permissions