Gurus, We are developing ETL packages to load from source feeds that are been provided. The file is as described below. File has 3 sections. a.) Header b.) Detail c) Footer Header : There will be only one header record in the file which have file level information with this. Header also has Hash value and Hash count information which can be used for file sanctity checks. Footer: There will be only one Footer record in the file. Detail: has 6 column data delimited with inverted exclamation sysmbol... 002¡GBP¡YEN¡TT¡13.28¡.2¡.2¡13.08¡13.48 PS: The file extension is .dat We want to load the Detail section in to a table. Need your help / suggestions to do same. We are thnking of writing a script component to load data by parsing line by line while ignoring header and footer rows.. but will it be a performance degradation? LEt us know We are using SQL 2005
The most correct behavior you will achieve with the Script Component as Source, where you can create several outputs, one for header, one for data and one for footer. If the script is properly written, you should not see any performance degradation. In fact all the SSIS components are written in .NET and even your script component is compiled code when executing and if properly written the performance will be OK. If you do not need to handle footer, there could be other possibility: 1. Write script task, which processes the header row 2. In subsequent data flow, use a Flat File connection manager which proper delimiter and **`HeaderRowsToSkip`** set to number of header rows 3. Because of the footer you will receive an error message, that last row is incomplete, but you can ignore the message. - You will receive this, because the structure of the footer is different. When processing, the pipeline skips the incomplete rows (the footer will be skipped). You will only receive warning in execution results during execution, that there are incomplete rows at the end of the file.