SSIS Talk

New in the 2012 version of SSIS is a feature that lets us keep a user-configurable amount of deployed project versions in the catalog. The problem, and the point of this blog post, is that you cannot export an older version of a project without reverting back to that version. This may not be possible in your environment and this post is an effort to provide you with another option.

First, this is what I am referring to:

When you right-click on a project under “Integration Services Catalogs” in SSMS, you can choose to view its versions. From this window you can revert back to an older version while still retaining the latest version deployed.

If you want to export projects, you can do so by right-clicking on the project and selecting “Export…” In this window, just point it to an appropriate location. In my case, I saved it to Documents\SQLSaturday165. The resulting file you produce is an .ispac (Integration Services deployment package). A .ispac file is just a .zip. Rename it to use .zip instead of .ispac if you want to extract a single package out of a project.

If you want to export an older version of the project, you have two options – set the older version of the project to be the current version via the Project Versions window, or you can use an SSIS package to explicitly export the desired project version. I will demonstrate the SSIS package solution. The full sample and download links are at the end of the post.

Step One
Create a global, temporary table (##) on the SQL instance that has the SSISDB catalog.

CREATE TABLE ##projectBLOB (val VARBINARY(MAX));

Step Two
Open up the ExportProject.dtsx package and modify the OLE DB Source in the data flow to use the values from the following query:

SELECT ov.object_version_lsn AS project_version_lsn,
       ov.object_id AS project_id,
       *
  FROM internal.object_versions AS ov
  JOIN catalog.projects AS p
    ON ov.object_id = p.project_id
   AND p.name = N'SQLSaturday165_SSISDBInternals' -- replace with your project name
  JOIN catalog.folders AS f
    ON p.folder_id = f.folder_id
   AND f.name = N'SQLSaturday165_SSISDBInternals' -- replace with the folder name that contains your project
 ORDER BY ov.created_time DESC;
GO

Step Three
Edit the derived column component to set the path that you want to put the exported project in. Remember that in the derived column component you need to escape slashes. (“C:\Temp” should be written as “C:\\Temp”)

Step Four
Change the SSISDB connection manager object to point to your SQL instance containing the SSISDB catalog.

Step Five
Execute the package.

Step Six
Confirm your project was exported to the path entered in the derived column component. You should have a .ispac file with the name of your project in that location. From this point, you can double-click on it to launch the deployment wizard, or you can rename the file so that it has a .zip extention. If you choose to rename it to .zip, you will be able to individually pull out the packages.

The resulting .ispac output file from the export process.

Renaming the .ispac file to .zip allows you to inspect its contents.

Just a word of warning – THIS SSIS APPROACH IS NOT A SUPPORTED METHOD OF EXPORTING PROJECTS FROM THE CATALOG. YOU NEED TO BE A MEMBER OF SYSADMIN OR THE SSIS_ADMIN DATABASE ROLE. YOU TAKE SOLE RESPONSIBILITY FOR RUNNING THIS PACKAGE IN YOUR ENVIRONMENT. I AM NOT RESPONSIBLE FOR ANY DAMAGE THAT IS A RESULT OF RUNNING THIS PACKAGE. THIS IS AN UNSUPPORTED TECHNIQUE THAT USES INTERNAL, NON-DOCUMENTED PROCEDURES FOR EXPORTING PROJECTS.

With that said, I am merely posting this technique for educational purposes. The supported method for exporting an older version of a project is to revert to the older version and exporting it using the GUI in SSMS and then reverting back to the correct, current project.

Don’t miss out on your opportunity for a day of free SQL Server training! Come join us in Lincoln, NE, on Saturday, October 6th starting at 8:30 AM. The event will be held in Avery Hall on the University of Nebraska-Lincoln campus. There are 6 tracks with each having 5 sessions. That’s 30 FREE sessions to choose from, with industry experts and emerging leaders speaking about their passion! Don’t miss out!

I will be doing a presentation on the internals of the SSIS Catalog introduced in SQL Server 2012. For those that haven’t been much involved in the new catalog subsystem of SSIS, please plan on attending as I will be giving an overview and then going into some of the procedures, processes, and logging that are possible with this new framework. See you there!

http://www.sqlsaturday.com/165/eventhome.aspx

In the new SSIS 2012 catalog’s project deployment model, by default if you execute a package with the Execute Package control flow task, or through the new API’s start_execution, the call will be made asynchronously.

In order to call the package synchronously, and therefore keep the caller waiting until the package finishes, you have to set a parameter value to true. This parameter, SYNCHRONIZED, is very, very sparsely documented on the ‘Net, but you can see it here assuming you’ve already executed some packages in your catalog:

SELECT DISTINCT parameter_name
  FROM SSISDB.catalog.execution_parameter_values  
 WHERE object_type = 50 -- 50 == execution parameter

To call it, simply call catalog.set_execution_parameter_values before calling catalog.start_execution and set the SYNCHRONIZED parameter to true (1). Like so:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  -- execution_id from catalog.create_execution
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value= 1; -- turn on synchronized execution

While I’m here, the other execution parameters are as follows:

• DUMP_ON_EVENT
• DUMP_EVENT_CODE
• LOGGING_LEVEL
• CALLER_INFO
• DUMP_ON_ERROR

DUMP_ON_EVENT – Boolean – 0* | 1 – Used with DUMP_EVENT_CODE, this signals to the SSIS engine to create a dump when the specified DUMP_EVENT_CODEs are encountered.

DUMP_EVENT_CODE – String – “”* – Used with DUMP_ON_EVENT, a semi-colon delimited list of event codes that will generate dump files if encountered. If we wanted to capture login failures and unable to connect event codes, specify respectively: 0x80040E4D;0×80004005.

LOGGING_LEVEL – Integer – 0 | 1* | 2 | 3 – Sets the logging level for this execution. The values represent no logging, basic, performance, and verbose levels respectively.

CALLER_INFO – String – “”* – Used to pass-in additional information on the caller. Helpful when viewing the overview of an execution in the built-in reporting provided with the catalog.

DUMP_ON_ERROR – Boolean – 0* | 1 – If an error is encountered, create a dump.

* – default value

See below for a complete example of the above parameters.

DECLARE @execution_id BIGINT = 0;

-- Create a package execution
EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx', 
        @execution_id=@execution_id OUTPUT, 
        @folder_name=N'PhilsTest', 
        @project_name=N'Demo', 
        @use32bitruntime=False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value=1; -- true

-- Set our package parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_EVENT', 
        @parameter_value=1; -- true

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_EVENT_CODE', 
        @parameter_value=N'0x80040E4D;0x80004005';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'LOGGING_LEVEL', 
        @parameter_value= 1; -- Basic

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'CALLER_INFO', 
        @parameter_value= N'Phil''s Demo';

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_ERROR', 
        @parameter_value=1; -- true

-- Start the package
EXEC [SSISDB].[catalog].[start_execution] 
        @execution_id;

To confirm that they were accepted, here is a screenshot from the execution overview report:

Those of you running SSIS 2012 may not yet be pushing the limits of the RTM release, and while I can’t tell you where the limit boundary lies, I can give you advanced notice about a fairly big problem.

The entire SSISDB catalog is linked together via foreign keys, most of which are all linked to a single ancestor table – internal.operations. Almost all descendants of that table are set to cascade delete when removing data from internal.operations. Enter the SQL Agent job, “SSIS Server Maintenance Job.” This job by default is set to run at midnight daily, and uses two catalog parameters to function: “Clean Logs Periodically” and “Retention Period (days).” When these are set, the maintenance job purges any data outside of the noted retention period.

This maintenance job deletes, 10 records at a time in a loop, from internal.operations and then cascades into many tables downstream. In our case, we have around 3000 operations records to delete daily (10 at a time!) that translates into 1.6 million rows from internal.operation_messages. That’s just one downstream table! This entire process completely, utterly locks up the SSISDB database from any SELECT/INSERT data.

With that said, what does it mean to you? Potentially the cancellation of other SSIS packages that are running at the same time. For us, each and every time the maintenance job runs, packages get canceled. This is not good.

What can you do? For starters, move the maintenance job schedule to a more appropriate time for your environment. If you are like us and you have packages running 24×7, and there is no convenient time, then what? Not much really. At that point, you can decide not to run the maintenance job and hence accumulate records in SSISDB, or you can file a case with PSS (to add weight to the case I already have open).

Some details on the issue – I suspect based on some SQL Profiler tracing that the executable running the package is timing out waiting for its lock request to insert data into an SSISDB table. This timeout is not handled well, obviously, and needs to be reworked. The product team is aware of the issue. Please let me know if you’ve run into this problem and what you’re doing about it.

For those that are curious, here is the tangled web that is the SSISBD schema: