I developed an SSIS package that loads invoices, statements, pro-formas, etc. into a SQL Server table. The invoices are .pdf files and the business required that no identical documents were allowed to be stored. To determine uniqueness I simply created a hash of each document and used it as the unique identifier. I used SHA1 as the algorithm, which provided a 20-byte fingerprint and all was right with the world. That is until the developer working on the project had difficulty using that fingerprint from the front end.
An application was built that enabled users to meander through the data and view the .pdfs based upon criteria they selected. Once they narrowed down the field of valid documents, they would click on one or more and it would be pulled from the system and displayed. The problem was there seemed to be no way for the front-end to properly store the fingerprint, so when the user selected the document to view, the front-end would not send the proper fingerprint back to the server. The 20-byte fingerprint seemed to get all messed up. To the credit of the application group they tried everything, but to no avail. This meant I needed to add an identity column to the table. Easy-Peasy. Then I remembered all of the ancillary data.
There were a few tables that included additional data about the document, such things as the company it belonged to, date of the invoice, and all the things that make life easy for a user to narrow down the millions of documents they need to comb through to get the few they need. Since the front-end would be combing through this, I could not longer use the fingerprint, but had to make sure the ancillary tables were all based upon the identity column, which I aptly named: Webunique.
I hit a snag in the beginning on how to do this. I couldn't use the OLEDB destination to insert the document since there was no way to get the identity column for that record, so I had to write my own using a Script Component.
I broke the Script Component out in three pieces and will play them in reverse order, which should make it easier to follow. The first image will be of the ProcessInputRow.
The key to extracting the identity column is combining the Insert statement and the select statement in one sql command, separated by semi-colons. I then execute the combined statements using ExecuteScalar since I should get back a single value, which will be from identity select. It is all in the same scope, which is important and also took me the longest to figure out. Now that I have the identity in a variable, I need to pass that up and out of the Component. This is done in the PostExecute method.
To square things out, the image below shows the additional library (System.Data.SqlClient) needed as well as how to set up the connection string. I hope this helps solve problems you may be having in the field or expands your knowledge of what can be done with Script Components.
