question

Sarav.r avatar image
Sarav.r asked

SSIS inverted Exclamation delimiter

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
sqlssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sarav.r avatar image Sarav.r commented ·
Hi, But How to handle Inverted Exclamation...Actually I can ignore header and footer rows.. i just need to load the detail (data).. How I shall type this "Inverted exclamation".. any help will be high value
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Simply put the inverted exclamtion mark as delimiter. You can put whatever character as delimiter in the Flat File Connection Manager.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@Sarav.r I think the shortcut to that is ALT+0161. @Pavel Pawlowski +1. But I do not think it would be always be a warning message displayed as per the dependency of the maximum error count and any actual error in the file? So would it be a safe approach?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
Another approach could have been (if script component is not to be used), Put the file data in a staging table as a single column with "HeaderRowsToSkip" option. Filter the footer row from that table. Then the rest is quite easy to achieve, as the options could be quiet a few.
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
@Usman Butt, this could be also a way, but then you again have to parse the columns somehow. But it depends on the needs.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.