Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Flat File Import - CSV Files with empty information Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 8:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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
Posted Tuesday, October 9, 2012 10:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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
Posted Wednesday, October 10, 2012 12:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:47 AM
Points: 199, Visits: 512
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!
Post #1370702
Posted Wednesday, October 10, 2012 12:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 AM
Points: 5,073, Visits: 11,849
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1370715
Posted Wednesday, October 10, 2012 3:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
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 »

Add to briefcase

Permissions Expand / Collapse