Notes on SQL

In the previous article I covered the creation of the SSISDB – a new feature within SQL Server 2012 that is used to store all of the required information for an SSIS package.

This article will cover the creation of a basic SSIS package that will require parameters to move data from a table on a database from one server to another. This package will be stored in the SSISDB and a job created to execute it.

Creating the Test Project

To create the test database I merely used the ‘New Database…’ wizard and left the defaults alone. I have a database named ‘TestDB_INST1’ on the server ‘SQL2014_INST1’ and a database ‘TestDB_INST2’ on the server ‘SQL2014_INST2’.

Within each database is a very basic table that the SSIS package will use to copy from one database to the other. The details of the table are unimportant – it the creation of the SSIS package, importing it into the SSISDB and the job creation that I’m concerned with here.

So, creating an OLEDB source and destination with connections for the source and target databases:

I’ve also added a TRUNCATE TABLE task for the target, so when I switch servers around I’ll have an empty target table every time:

In the past I’ve used dtsconfig files to control the parameters for an SSIS package. Because I haven’t yet worked out how the later 2015 Data Tools do that, I’m still going to create the dtsconfig file. this is done by going through the menu – ‘SSIS’/’Package Configurations….’ and selecting ‘Enable Package Configurations’. This executes the all-too-familiar wizard for selecting which properties you want to control via an external file (in this case I’ve left it to the default of ‘SSISConfig.dtsConfig’). The properties I have selected here are ‘InitialCatalog’ and ‘ServerName’ for both of the connections, allowing me to specify the sql server instance and database for each connection.

An HTML file is created with these properties and in the past I would create a job to execute the SSIS package and set an entry in the ‘Configurations’ tab of the job to list the dtsConfig file. Now, the import wizard for the SSISDB will use this file to create parameters in the format that it requires.

Importing the Test Project into SSISDB

To import the SSIS package into the SSISDB, within SSMS expand the ‘Integration Services Catalog’ tree to the ‘Projects’ level. Right-click on the ‘Projects’ node and select ‘Import Packages…’, which will launch the wizard for this task.

The first screen wants to locate the SSIS package that you intend to import. In my case I saved it in the C:\Temp folder beforehand, to save any long-winded hunting.

The next page locates the dtsx file and you can confirm which one to import (if your project has several).

Then it requires a location to place the files it generates for the import, a name for the project, protection level and a description (the description isn’t mandatory).

The next page (‘Update Execute Package Task’) is empty in this example and the page after that locates the dtsConfig file for this dtsx package.

Having located the dtsConfig file it then shows the parameters that it has extracted from that and allows you to select which you require.

Following that is a screen that gives you the opportunity to change the values for the specified parameters. These can be change later in a variety of ways, so don’t worry that this is your one chance to set them.

A summary screen follows and then a progress screen. Once completed it has created the files required to import into the SSISDB, which it then continues to do with the second stage of this wizard.

It now processes the import file, from the location you specified earlier.

Specify the server you want to import this package into and the path within the SSISDB for that server.

Another summary screen appears before the import is complete. Now you can see the package detailed in the required location under ‘Integration Services Catalog’.

At either the ‘Copy Table’ node or ‘Package.dtsx’ I can right-click and select ‘Configure…’ to change the values for the parameters for this package. Note that the ‘Scope:’ entry on the wizard default changes, depending upon which node I clicked on.

However, you can also change the parameters from within the Agent job.

When creating a job to execute this package I need to specify ‘SSIS Catalog’ for the Package Source.

Whereupon I can also change the parameters for the next execution of this job via the ‘Configuartion’ and then ‘Parameters’ tab.

Conclusion

Although a bit more work then the previous method of storing SSIS packages, the SSISDB uses a wizard to store everything required within one database. Upon first inspection I quite like it, but there isn’t a massive amount of helpful documentation on the inner workings of this.

In following articles I’ll demonstrate how to copy the entire SSISDB to another server, how to move individual SSIS packages between servers and how to use T-SQL to extract information about the packages from the SSISDB.