Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Flat File Import - CSV Files with empty information


SSIS Flat File Import - CSV Files with empty information

Author
Message
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
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.
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
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 -
sqlusers
sqlusers
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
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! :-)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Tava
Tava
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 774
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search