Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data flow, it appears that those rows are still being validated; so that if they contain invalid data(blank rows), the datasource fails. This prevents using a conditional switch to remove rows, since the data flow never starts.

Is this accurate description of the normal functioning and is there any way to work around it?

Sample data looks like -

COL1|COL2|COL3

abc|123|10
def|456|20
ghi|789|30

Error Configuration

share|improve this question
    
please see my comment.. –  Kin Jun 12 '13 at 19:11
    
Not sure which you are referring to here. If I add two delimiters to the blank line, the source succeeds, meaning it is validating those rows, even if it intends to skip them. –  Jeff Sacksteder Jun 12 '13 at 19:30
add comment

2 Answers

up vote 3 down vote accepted

You're reading it backwards. You have instructed it to skip the first row of data and it does that. It then looks in the next row for column headers, which it won't find because it's blank. The actual data should be found on the third row. It tries to clue you in when you look at your preview. I create two files, one with your data and the other with the first line blank and the headers in the second row.

CM for blankfirstrow

Notice we are previewing rows 2-4 Columns for blankfirstrow

Contrast that with your blank in between header and data

enter image description here

Still previewing rows 2-4 but the default header names are the first rows of data. Not quite what we're expecting.

enter image description here

When I created a simple Data flow using the two connection managers, it fails (as expected) on the BlankSecondRow as the above definition performs some validation that the header names are actually there. If I uncheck "Column names in the first data row" and up the "Header Rows to Skip to" 2, then it will work.

enter image description here

share|improve this answer
add comment

You have to select in properties --> DELAY VALIDATION = TRUE. By default it is set to false. That will take care of the issue you are facing.

UPDATE :

Also, in conditional split Editor, you can put an expression to skip blank rows :

e.g column1!=""&&column2!=""&&column3!=""

or you can use Row Number Transformation and using conditional split you can take first few (e.g. 10) records based on the variable created by the component.

share|improve this answer
    
I think the meaning of that is 'don't validate at design time'. The validation still happens at execution and apparently that includes the first few rows of the data, including the ones I wanted to skip. In any event, I still have the error after trying it both ways. –  Jeff Sacksteder Jun 12 '13 at 18:15
    
@JeffSacksteder Can you post a screenshot of the error and your sample flat file ? I can help you with that. –  Kin Jun 12 '13 at 18:21
    
I can't do anything in the dataflow, since the source source fails validation. –  Jeff Sacksteder Jun 12 '13 at 19:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.