Category: Integration Services

John McCormack does some spring cleaning:

SQL Server Integration Services (SSIS) is a powerful tool, but migrating packages across SQL Servers can be a slow and thankless task if you don’t use automation. The single best way to do this is by using DTUTIL, a command-line utility provided directly by Microsoft. When it comes to moving 1000s of packages, I would only use DTUTIL because it can achieve in minutes what it would takes days to achieve using point and click.

Read on to see how you can move these packages between instances, as well as downloading packages as a backup.

Comments closed

Meagan Longoria deals with the unpleasantness:

Although you can now specify an Oracle database using the EzConnect format, it’s still very common to use a tnsnames.ora file. This file specifies a connection name and connection details for an Oracle database. It’s helpful in that you can provide a “friendly name” to the database specified in the file and then reference the file from multiple client tools.

Read on to learn how you can use it.

Comments closed

Thomas Williams checks the logs:

Due to its simplicity, I’m a fan of the default, in-built “SSIS log provider for SQL Server” logging, which writes to the SSIS database SSISDB when the package is deployed to a SQL Server. This logging comes out of the box with very little setup required, and can be supplemented by custom messages – for instance, using Dts.Log in a script task as per https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/logging-in-the-script-task.

Best of all, because log messages are written to tables in the SSISDB database, end-users can run a query or report to troubleshoot errors.

Read on for a query which pulls the last seven days worth of error information from the built-in log.

Comments closed

Andy Leonard beats the brakes off of some stuff:

It may help to define reference first. An SSIS Catalog Reference is a relationship between an SSIS Project and an SSIS Catalog Environment in the SSIS Catalog. One or more references to environments may be configured for a project. At execution time one and only one reference may be selected – like Highlander, “There can be only one.”

A broken reference occurs when one configures a reference between a project and an environment, and then the environment is deleted. 

Read on to see what this looks like in the SSIS Catalog Browser and SISS Catalog Compare tools, as well as how you can prevent accidentally creating broken references.

Comments closed

Peter Schott shows off a handy trick:

I’ve worked with SSIS for some time now, but a recent question and post pointed out an expression I’d never used before. We often have a need to split strings and pick out some portion of that string. Sometimes we have a need to pull in everything in the “nth” occurrence of a string. The TOKEN expression can be used to get that particular value. I tested this out by mocking up a really simple package.

Read on to see how.

Comments closed