October 9, 2012 at 8:49 pm
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.
October 9, 2012 at 10:53 pm
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 -
October 10, 2012 at 12:24 am
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! 🙂
October 10, 2012 at 12:51 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 10, 2012 at 3:02 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply