Skip 'x' number of rows while reading flat file

Question

Skip 'x' number of rows while reading flat file

aveerabadran

Ten Centuries

Points: 1271
More actions
March 9, 2009 at 7:42 am

#129559

Hi,
i have a requirement of skipping 'x' number of rows , when there is error during flat file load .
Consider the following example :
Say, i am loading table T1 from flatfile F1 (100 records) and flatfile F2(100 records) . F1 is loaded into T1 using Package1, and F2 is loaded using Package2.
I load all the 100 records of F1 file successfully using Package1. But during loading of flatfile F2 using Package2, 50 records gets loaded successfully(gets commited) , but 51st record loading fails due to tablespace issue. Later tablespace issue is resolved and when i resume the loading using using Package2, i shud be able skip 50 records of flatfile F2 ,and load from 51st record.
my question is : is there a way to pass the number of records to be skipped as a parameter to the package while starting?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

321 MySQL Right there with Babe Points: 750 More actions · Answer 1

aveerabadran (3/9/2009)
Hi,
I load all the 100 records of F1 file successfully using Package1. But during loading of flatfile F2 using Package2, 50 records gets loaded successfully(gets commited) , but 51st record loading fails due to tablespace issue. Later tablespace issue is resolved and when i resume the loading using using Package2, i shud be able skip 50 records of flatfile F2 ,and load from 51st record.
my question is : is there a way to pass the number of records to be skipped as a parameter to the package while starting?

hi,

in advance sry for my bad english, i hope you´re able to understand !

yes you can, right click in the SSIS and "package configuration", give the configuration-File (XML) a name. Then navigate to the Flatfile-Connection and where "DataRowtoSkip" to make a "x". Then you ´re able to add in the XML-File the number of records to skip 🙂

i hope this was helpful (or helpfully) for you, and don´t hesitate to ask any questions.

Greetings

Tim Mitchell SSCoach Points: 15681 More actions · Answer 2

Instead of trying to skip n records and re-run the package, you should consider using the Error Output of your destination component. This will allow you to send the rows that are in error to a separate destination, such as an error table or file. You'll need another package (or at least another Data Flow component in your existing package) to readdress those error records, but it's likely the easiest route.

To answer your question directly, it is possible to skip a variable number of rows, but I suspect that doing so would require more effort and would certainly be more prone to errors than using the Error Output method.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 3

You may want to consider loading the data into staging tables first. That way you don't have to worry about such stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ulrich Bauhofer Say Hey Kid Points: 700 More actions · Answer 4

Hi

you can set the property 'HeaderRowsToSkip' in the flatfile-connection you use (SkriptTask?).

aveerabadran Ten Centuries Points: 1271 More actions · Answer 5

Thanks all for ur replies..

The # of rows to skip will vary , and in the production environment , we will not have priviledge to change the ETL packages. Thats the reason we asked if it is possible

- to pass the 'rows to skip' in a variable during run time

or

- update a column in a table , whose value will be read and stored in a variable and which inturn will be used to skip the rows.

~Thanks

Arun

321 MySQL Right there with Babe Points: 750 More actions · Answer 6

yes you´re able to store the value DataRowtoSkip in a table like Default SSIS Configurations. right click in the Package..Package Configurations .. add.. Configurations Type Sql Server..then specify the connection and the table .. next and then set properties of the flatfile dataRowToSkip enabled .. set a name und then the table with the value was build

i hope you can follow me in this desciption, otherwise don´t hesitate to write what was not clear for you