There doesn’t seem to be a way to turn off the logging of PackageStart and PackageEnd events in the SSIS Logging GUI in Visual Studio (correct me if I’m wrong). If you don’t care about these events and your ETL runs often this can result in a lot of excess log entries. Thankfully if your logging to SQL Server rather than a CSV file for example you can simply customise the stored procedure that SSIS uses to write log entries.
Modifying Sp_SSIS_Addlogentry to not write PackageStart and PackageEnd events
Sp_SSIS_Addlogentry is the stored procedure SSIS uses to log entries in your SQL Server DB. It will attempt to create this sproc and corresponding log entry table (sysssislog) if they don’t currently exist. If they do exist SSIS will not overwrite them so your customisations are safe.
If the Visual Studio GUI doesn’t provide the level of granularity you need to turn logging of certain events on or off you can just ignore these events manually by altering the Sp_SSIS_Addlogentry stored procedure. For example line 16 and 17 below shows both PackageStart and PackageEnd being ignored and the sproc returning before the insert statement.
Useful customisations to Sp_SSIS_AddLogentry
By modifying the Sp_SSIS_AddLogentry sproc you have full ability to intercept all logged events and do what you want with them. Some other customisations I’ve done in the past include:
Log the dates as UTC as they’re currently stored in DB local time.
Control the frequency of certain kind of events such as PipelineComponentTime. PipelineComponentTime is very useful for seeing how long each of your tasks take but it does log a lot of entries. If you only wanted snapshots you could just log every X amount of minutes for example.
When setting up a log file connection to be used as an SSIS log file provider if you statically declare the connection string the file will just grow and grow continuously. Depending on your scenario this file could become huge and make finding entries for a particular date and time difficult.
Fortunately we can use expressions to build the connection string dynamically and have SSIS write to a file whose name is based on the current date. To do this click on the file connection and from the Properties window add a ConnectionString expression to build a file name string with the date embedded. A sample expression for YYYY-MM-DD format is below.
When using the Union All transformation editor in SSIS you’ll notice by default each input column is just named ‘Union All Input 1’, ‘Union All Input 2’ etc. (but not necessarily in that order) as shown below.
If you’ve got a lot of inputs this can be somewhat confusing as its harder to see which column corresponds to which of your source inputs. It’s easy to give your inputs a custom name however you can’t do it through the IDE as Visual Studio has the relevant Property (DestinationName) disabled. I don’t know why this is but I would be happy if anyone can tell me.
Updating DestinationName manually
In this case you can just manually edit the .dtsx package. Just search for name=”Union All Input X” and change that to whatever you want. In the below example I’ve changed DestinationName to ‘ABC Input’.
After you save and reload the package (you might have to reload the SSIS project in Visual Studio too) and go back into the Union All Transformation Editor you’ll see your custom input name.
In work, our DEV and our UAT DB environments reside on the same box, but with their own SQL instances. We prefer to store SSIS packages on disk as they are a little (actually a lot) easier to manage that way. Since our DBs are on the same box we could, when configuring SSIS packages just copy the packages to two separate file locations for DEV and UAT and alter the design time package configuration to point to DEV.dtsconfig for one and UAT.dtsconfig for the other.
Rather than this however we attempted to just have the SSIS package in one location and override the default .dtsconfig file which was defined at design time by passing in the the environment specific .dtsconfig as part of the two (DEV & UAT) SQL agent jobs set-up to execute the package on schedule. This can be done from the configurations tab in the job properties.
To get the .dtsConfig specified in the job to be the effective one, we needed to disable package configurations in design time. Not delete the existing one, but just disable it. After that the config file specified in the SQL Agent job was used.
As we know using absolute paths in our code can complicate things from a deployment point of view, so it’s best to use relative paths were possible. In business intelligence studio 2008 however the package configuration wizard doesn’t allow you to enter relative paths when pointing to configuration files. If you attempt to type a relative path in, clicking next will replace it with the absolute path, so we appear to have a problem here.
There is an easy work around however, rather than using the GUI just edit the .dtsx file directly to point to the relative path. For example if in BIDS package configuration window you have entered the path as ‘c:\SSIS\active.dtsconfig’, simply change that to ‘..\SSIS\active.dtsconfig’ using a text editor and next time you open the wizard the relative path will be used.