CODEWITHSUNDEEP
gitssisbiml
Michael K
Michael K

Reputation: 439

How to conduct mass updates to SSIS packages via BIML?

Here's the scenario I'm having trouble with:

Let's say I used BIML to generate (stub out) 50+ SSIS packages ... For simplicity's sake, lets say they each have only one Execute SQL Task.

Package1
-> SQL Audit (INSERT INTO auditTable(val1) values ?)
Package2
-> SQL Audit (INSERT INTO auditTable(val1) values ?)
...
Package50
-> SQL Audit (INSERT INTO auditTable(val1) values ?)

Now, I make package-specific changes to each package...

Package1
-> SQL Audit (INSERT INTO auditTable(val1) values ?)
-> Add Dataflow: DFT Stage Data
Package2
-> SQL Audit (INSERT INTO auditTable(val1) values ?)
-> Add Expression: EXP SET Variable Value
...
Package50
-> SQL Audit (INSERT INTO auditTable(val1) values ?)
-> Add Script: SCT Call Webservice

At this point, I've created 50 packages using the BIML template with (INSERT INTO auditTable(val1) values ?)...

but I get a new requirement to store a second column in the auditTable(val1, val2) (INSERT INTO auditTable(val1,val2) values ?,?)

So I regenerate my packages 1->50 using an updated BIML template... but - in doing so - I've lost all of the package-specific customization (Add dataflow, add expression, etc).

Package1
-> SQL Audit (INSERT INTO auditTable(val1,val2) values ?,?)
Package2
-> SQL Audit (INSERT INTO auditTable(val1,val2) values ?,?)
...
Package50
-> SQL Audit (INSERT INTO auditTable(val1,val2) values ?,?)

So my question is...

What is the easiest way to deal with this?

As far as I can tell my options are as follows:

** I know there are better ways to design this... For example, a more future-proof stored procedure instead of an INSERT INTO, but I am only interested in answers the core issue - which is how to selectively merge specific parts of a file from a previous version after it has been overwritten.

Upvotes: 2

Views: 355

Answers (1)

billinkc
billinkc

Reputation: 61221

Generating an SSIS package via Biml is an all or nothing exercise. The biml compiler knows how to translate biml into dtsx. There is no facility for generating a dtsx and then comparing it to another and merging. The assumption is that the true shape of the package is encoded in the Biml.

The challenge with the approach you took is that you've severed yourself from the generation process - no knock on what you've done, when I was learning I did the same thing. A more Biml-ic approach would be to have modified the original Biml, using metadata and conditional to shape individual packages.

So, what can you do now?

1) Reverse engineer your final packages into Biml. This is now built into the free BimlExpress product so it's merely repetitious rather than being a cost factor. Once you have all your packages in Biml, you could enumerate through all your package nodes look for an Execute SQL Task that matches your target and fix them. If it's really something like auditing, I would abstract that out of all of my individual packages and make it an "includes" for each of them. That way when this changes a third time, you make a single change and re-emit your SSIS packages.

And additional thought would be to use BimlStudio and let a Transformer redefine the Execute SQL Task nodes but that gets you back into a paid-for approach. Sample of using a transformer to update the value is linked there.

2) Emit a package that has the corrected Execute SQL Task and all the associated bindings and then pick your poison of choice to edit the text within the packages. I like TextPad/WildEdit but anything will work as long as it can find this block of text and replace it with this new block.

Upvotes: 2

Related Questions