Why can’t I create SSIS Project Parameters from Biml?

Why can't I create SSIS Project Parameters from Biml in BimlExpress or BIDS Helper?BIDS Helper and BimlExpress does not support creating SSIS project parameters from Biml out of the box. There are workarounds (and I have previously blogged about my solution for creating project parameters from Biml), but why is this not a standard feature in BIDS Helper or BimlExpress? Many people have asked about this, so I sat down with Biml creator Scott Currie (@ScottCurrie) to get the full story.

Why doesn’t BIDS Helper or BimlExpress emit SSIS project parameters from Biml?

Well, technically it could, but it shouldn’t. The user experience would have serious issues, leading to confusion, frequent errors, and the potential for data loss. How can that be?

First a comparison to SSIS packages
When the BimlEngine emits a package, it knows that it is overwriting the entire .dtsx file. In general, this is safe, because each .dtsx file contains exactly one package. There is no risk that by overwriting one .dtsx file, that it might overwrite both the desired package and some other unrelated package. One package per file – good.

Furthermore, since it is one package per file, BIDS Helper or BimlExpress can present you with a convenient checklist of all the packages that are about to be overwritten. Maybe you made a mistake in your BimlScript code and accidentally generated a package that has the same name as a painstakingly created manual package. Maybe you noticed it in the overwrite list. Maybe not. Either way, you had the opportunity to prevent that bad thing from happening. Also, if you are using source control and checked in recently, you can revert the changes and restore your manual package.

Finally, if you have a package open with unsaved edits, not much changes from the above scenarios. The very uncommon worst case is that you lose a small number of changes that you made to a manual package since you last saved – and only when you also made the mistake of generating a package with the same name as your manually created package. The common case is that you lose some manual changes (e.g. moving boxes around on the design surface) that you never intended to preserve in the first place.

How are SSIS project parameters different?
SSIS project parameters do not work the same way as SSIS packages. All project parameters are stored as XML elements in a single XML document for the entire project called Project.params. This is the core reason why packages have a good overwrite story while parameters have a poor overwrite story.

It should be obvious that BimlExpress can’t just overwrite your Project.params file. Of course, BimlExpress would be creating the parameters you specified in your BimlScripts, but it would also be overwriting any parameters you might have created manually. If you are a Biml purist, you might not care about this, because you would be fine with creating all of your project parameters through Biml. Unfortunately, most Biml users are not Biml purists – and even fewer development teams are Biml purists.

The next logical thought is to avoid the overwrite problem through merging. Certainly BimlExpress could insert the generated project parameter elements into the existing Project.params file. Duplicate values could be ignored. Unfortunately, this only solves the problem for the very first version of your generated Project.params file. What happens when you change the names of the parameters in your Biml code or delete some of them? The BimlEngine has no way of knowing that a given parameter in your Project.params file was deleted or renamed and not just created by hand. This would lead to a potentially large number of orphaned project parameters that you would need to manually manage.

Perhaps, you might suggest, we could add some form of XML annotation to the Biml-generated elements in the Project.params file to solve or at least improve the merging capabilities? That could be a great solution, but Visual Studio / SSDT strips any additional properties you add whenever it saves the Project.params file. Even if Visual Studio / SSDT preserved those additional annotations, this could still be a risky strategy, since other Microsoft and 3rd party tools have the potential to fail or otherwise misbehave if the BimlEngine diverges from the standard accepted encoding for the Project.params file.

This gets even worse for project parameters in the scenario where the user has unsaved manual changes to the Project.params file at the time when generation is performed. These unsaved changes are impossible to detect and merge because the SSIS project system does not expose those changes to add-ins until they have been saved in the Project.params file. This means that parameter changes would have to be force-saved prior to any Biml generation, but would then still suffer from all of the above issues.

Can’t you do anything? Even with an optional setting?
Based on the above analysis, the only scenario that doesn’t create more user confusion and frustration is the Biml Purist scenario. For this case, we might be able to offer an option to always overwrite the Project.params file, but that would satisfy a minority of users and would also be very frustrating in cases where you forgot to turn it off for non-purist projects. Our thinking is that it is not worth the trouble it might cause to add this option.

Thank you, Scott!

Scott CurrieThank you to Scott Currie (@ScottCurrie) for taking the time to give us such a detailed explanation! I really appreciate it, and I hope this helps all of you Biml users who have been wondering why you can’t create SSIS project parameters from Biml. (Well, you can, but as you have probably realized by now – it can be risky.)

Finally, both Scott and I would love to hear your thoughts on this. Would you like to see an optional setting? Is it worth the risk, especially when working with Biml-generated packages and manually created packages? Is it worth the risk when working in mixed development teams?

What do you think?

Create SSIS Project Parameters from Biml

Project Parameters from BimlIf you are using BIDS Helper or BimlExpress to generate SSIS packages in the Project Deployment model, you have probably noticed that it is not possible to create project parameters from Biml. You can write Biml for the project and project parameters, but BIDS Helper / BimlExpress will only generate the SSIS packages for you and not the SSIS project parameters. The recommended solution is that you create the project parameters manually before you generate your SSIS packages from Biml.

(Want to know why? Read Scott Currie’s explanation in my blog post Why can’t I create SSIS Project Parameters from Biml?)

However, if you are a lazy developer like me, you probably don’t want to create and update project parameters manually. Perhaps you want to automatically create or update project parameters based on some metadata? You can do that!

Let’s take a look at a (semi-hardcoded, semi-hack) solution for creating SSIS project parameters from Biml in BIDS Helper / BimlExpress :)

Continue reading →

Batch update properties in SSIS packages (T-SQL Tuesday #68)

T-SQL TuesdayT-SQL Tuesday #68 is hosted by Andy Yun (@SQLBek). Many SQL Server defaults are not ideal, and most of us have a list of defaults we always change. Andy wants us to Just Say No to Defaults and blog about what, why or how we change defaults.

If you are an SSIS developer like me, there is a big chance that the ProtectionLevel in SSIS Packages is on top of your list of defaults to change. The default ProtectionLevel is EncryptSensitiveWithUserKey (ugh), but most of the time it is not the best option. Raise your hand if you have ever asked your favorite search engine for advice on issues like “SSIS package fails in SQL Server Agent job” or if you have ever heard someone exclaim “but it works on my machine!?” :)

There are many great blog posts about the different ProtectionLevels, why you probably want to change to DontSaveSensitive as your default, and how to use configurations and parameters instead of encrypted SSIS packages. I will not go into details about any of that in this post, but I will use ProtectionLevel as an example default property you want to change in many SSIS packages at the same time.

How do you batch update properties in existing SSIS packages? You probably don’t want to open up every single package and change them manually?

Continue reading →

SQL Server, SSIS and Biml Data Types

This post was first published on May 27th, 2014, and was last updated on July 16th, 2017.

SQL Server, SSIS and Biml Data TypesThe table below shows a simplified mapping between SQL Server, SSIS and Biml data types. The table does not include all possible mappings or all data types, but is meant as a quick reference while developing and learning Biml. It was mainly created as a cheat sheet for myself, but I hope other Biml developers will find it useful :)

(Pssst, if you want to link to this post, you can use this prettier link: https://www.cathrinewilhelmsen.net/BimlDataTypes. Thanks!)

There are some problems mapping certain data types automatically between SQL Server, SSIS and Biml. Different providers and Biml methods will produce different data mapping results. I have written about the problems and differences I have encountered below.

All columns are sortable:

SQL ServerSSISBiml
bigintDT_I8Int64
binaryDT_BYTESBinary
bitDT_BOOLBoolean
charDT_STRAnsiStringFixedLength
dateDT_DBDATEDate
datetimeDT_DBTIMESTAMPDateTime
datetime2DT_DBTIMESTAMP2DateTime2
datetimeoffsetDT_DBTIMESTAMPOFFSETDateTimeOffset
decimalDT_NUMERICDecimal
floatDT_R8Double
geographyDT_IMAGEObject
geometryDT_IMAGEObject
hierarchyidDT_BYTESObject
image (*)DT_IMAGEBinary
intDT_I4Int32
moneyDT_CYCurrency
ncharDT_WSTRStringFixedLength
ntext (*)DT_NTEXTString
numericDT_NUMERICDecimal
nvarcharDT_WSTRString
nvarchar(max)DT_NTEXTString
realDT_R4Single
rowversionDT_BYTESBinary
smalldatetimeDT_DBTIMESTAMPDateTime
smallintDT_I2Int16
smallmoneyDT_CYCurrency
sql_variantDT_WSTRObject
text (*)DT_TEXTAnsiString
timeDT_DBTIME2Time
timestamp (*)DT_BYTESBinary
tinyintDT_UI1Byte
uniqueidentifierDT_GUIDGuid
varbinaryDT_BYTESBinary
varbinary(max)DT_IMAGEBinary
varcharDT_STRAnsiString
varchar(max)DT_TEXTAnsiString
xmlDT_NTEXTXml

(* These data types will be removed in a future version of SQL Server. Avoid using these data types in new projects, and try to change them in current projects.)

Continue reading →