I can speak SQL

Fluent SQL speaker

Archive for June, 2013

SSIS 2012 fail to start – execution timed out

Posted by kulmam92 on June 27, 2013

I’ve recently upgraded to SQL Server 2012 and created a centralized ETL server. Things were fine until I enabled the scheduled jobs. Jobs started to fail randomly after reporting the error below:

Started:  1:10:01 AM  Failed to execute IS server package because of error 0x80131904.
Description: The operation failed because the execution timed out.  Source: .Net SqlClient Data Provider  Started:  1:10:01 AM  Finished: 1:10:07 AM  Elapsed:  5.6 seconds.  The package execution failed.  The step failed.

I searched the web and found that I wasn’t the only one. Furthermore, MS has yet to release a patch for this issue. The relevant reported MS Connect post:

SSIS package fails to start – application lock timeout in SSISDB.catalog.create_execution

My environment

Name Value
Product Microsoft SQL Server Standard (64bit)
Operating System Microsoft Windows NT 6.1 (7601)
Platform NT x64
Version 11.0.3368.0
Memory 8191 (MB)
Processors 1

What I did

I started my investigation by checking dmv and ended up modifying one of the SPs in SSISDB. I know that this isn’t a good choice but did that to fix quickly.

Process Monitoring

I ran a query against sys.dm_exec_requests and got the following results. I noticed that there was a lot of blocking going on and the execution timed out error had occurred because of this.

Before_Change_Lock

I was able to track down that xp_userlock was being called by sp_getapplock in catalog.create_execution SP.

Index creation

Many have pointed out the problem of missing indexes on SSISDB. Phil Brammer shared a missing index creation script via SSIS 2012 – Catalog Indexing Recommendations. I was hoping this error was due to the missing indexes, so I simply applied Phil Brammer’s script instead of spending more time investigating. Unfortunately, it didn’t help and I still got an execution timed out error. At this point I thought it’d be better to use a different approach. I started to review the logic of catalog.create_execution SP thoroughly.

catalog.create_execution modification

Since I needed to fix this timeout issue in a single day, I decided to take an extreme route by modifying the catalog.create_execution SP. I will explain what I changed shortly, but first, the sys.dm_exec_requests query result. As you can see, the blocking issue has been resolved.

after_Change_Lock

You can download the modified code here : [catalog].[create_execution_new].

catalog.create_execution

Looking at the big picture, this SP generates data on four tables: operations, operation_permissions, executions and execution_parameter_values.

Logic flow

The developer of this SP took a procedural and very conservative approach to ensure the following two things: first, that data don’t change until insertion to execution and execution_parameter_values and second, that there was an assurance that different error messages can be returned for each step.

Transaction Lock Step No Main step Sub Step
1 Check if the sp(catalog.create_execution) is called using windows Authentication Returns an error if it’s called using SQL Server Authentication.
2 Insert data into operation table [internal].[insert_operation]
3 Insert data into operation_permissions table [internal].[init_object_permissions]
T X 4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
T X 5 Begin transaction
T X 6 Start application lock EXEC sp_getapplock @Resource = ‘MS_ISServer_Create_Execution’, @LockTimeOut= 5000, @LockMode = ‘Exclusive’
T X 7 Get project_id and version
T X 8 Get environment information Get environment information using reference_id
Check if there’s any discrepancy in data type or sensitivity between parameter and environment variable
T X 9 Update operations.object_id to project_id
T X 10 Insert data into executions table INSERT into [internal].[executions]
T X 11 Create key to decrypt and encrypt sensitive data CREATE CERTIFICATE
CREATE SYMMETRIC KEY
T X 12 Insert execution_parameter_values 1) insert non-sensitive parameter doesn’t reference environment variable
2) insert sensitive parameter doesn’t reference environment variable
3) Insert records for object_type=50(instance of execution)
4) insert non-sensitive parameter referencing environment variable
5) insert sensitive parameter referencing environment variable
6) update sensitive parameter value after converting data type if it’s date, double or decimal
7) insert any missing parameter from project level object parameter
8) insert any missing parameter from package level object parameter
9) update parameter value to object_parameters.design_default_value if sensitive=0 and required=0 and value_set=0
T X 13 close symmetric keys
T X 14 commit

Now, the Code

I modified the original code to assure the following principals.

  1. Keep SERIALIZABLE isolation level as short as possible
    • I assumed the developer wanted to guarantee that the same meta information for the package is retrieved until he finishes the data insert.
    • You don’t need to keep the SERIALIZABLE isolation level once you have necessary data
  2. Take out application locking
    • I assumed the SERIALIZABLE isolation level is enough to guarantee that meta data wouldn’t be changed during retrieval.
  3. Keep transactions as short as possible
    • A transaction is used only when DML is executed.
  4. Convert the procedural approach to a set-based approach
Transaction Lock Step No Main step Sub Step
1 Check if the sp(catalog.create_execution) is called using windows Authentication Returns an error if it’s called using SQL Server Authentication.
2 Insert data into operation table [internal].[insert_operation]
3 Insert data into operation_permissions table [internal].[init_object_permissions]
X 4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
X 5 Get project_id, version and environment information convert logic to single SQL statement
X 6 Create key to decrypt and encrypt sensitive data CREATE CERTIFICATE
CREATE SYMMETRIC KEY
X 7 Generate data for execution_parameter_values and insert into temp table convert logic to single SQL statement
X 8 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
T 9 Begin transaction
10 Update operations.object_id to project_id
T 11 Insert data into executions table INSERT into [internal].[executions]
T 12 Insert execution_parameter_values INSERT INTO [internal].[execution_parameter_values]
T 13 close symmetric keys
T 14 commit

Things that you should know:

  1. I took out the permission check logic. As for the reasons:
    • I worried that permission check logic might make the modified query too complicated.
    • In my environment I don’t have any need to set different permission settings for SSISDB objects
  2. I used base tables instead of views.
    • I wanted to be able to predict the execution plan and control access path.

Testing

If you capture what’s been called when you execute a package in an Integration Services Catalogs using the profiler, we will find that three SPs, [catalog].[create_execution], [catalog].[set_execution_parameter_value] and [catalog].[start_execution], are called. Below is the trace that I captured:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N’Package.dtsx’, @execution_id=@execution_id OUTPUT, @folder_name=N’DEV’
, @project_name=N’Test2′, @use32bitruntime=False, @reference_id=1
Select @execution_id

exec sp_executesql N’DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N”LOGGING_LEVEL”, @parameter_value=@var0
‘,N’@execution_id bigint’,@execution_id=10010
go
exec sp_executesql N’EXEC [SSISDB].[catalog].[start_execution] @execution_id’,N’@execution_id bigint’,@execution_id=10010
go

I created my SP [catalog].[create_execution_new] and checked if both [catalog].[create_execution] and [catalog].[create_execution_new] generates the same result. I also checked if the execution result of both were identical, and they were. I then created a backup copy of [catalog].[create_execution] and replaced [catalog].[create_execution] with my SP. It’s been several days since I made the replacement and it’s still working well.

Summary

I’m afraid that I had to choose an extreme workaround. However, it was a good chance to peek at both how the SSIS catalog works and what the developer was thinking. I hope this helps those who are experiencing the same issue and that a fix from MS is coming soon.

Posted in SQL SERVER, SSIS | Tagged: , , , , | 7 Comments »

SSISDB ERD – A Journey to DB deployment automation

Posted by kulmam92 on June 17, 2013

I decided to reverse engineer the SSISDB to have a better understanding of the SSIS catalog. Reza Rad posted a really good article called “SSIS 2012 Catalog series“. However, I thought it would be better to create my own version of an ERD to have better understanding of it. Here’s what i came up with.

SSISDB ERD

SSIS ERD

Yon can download the pdf version of this from SSISDB_ERD

Table

Main tables

Object related tables

There are four object tables: folder, environment, project and package. I think you can easily figure out the purpose of each table. Other than these key tables are the tables for relationship (environment_reference), parameters, versions and permissions. SSISDB keeps a project’s previous versions as binary data in the internal.object_versions table. You can specify how many versions that you want to keep by using the properties of an SSIS catalog.

image

[Operations]

This table contains one row for each operation in the Integration Services catalog. You can track all logical operations that were performed on the server using this table, such as project deployment or package execution. Below are the operation_type values from BOL:

operation_type Value operation_type Description object_id Description object_name Description
1 Integration Services initialization NULL NULL
2 Retention window (SQL Agent job) NULL NULL
3 MaxProjectVersion (SQL Agent job) NULL NULL
101 deploy_project (Stored procedure) Project ID Project name
106 restore_project (Stored procedure) Project ID Project name
200 create_execution and start_execution (Stored procedures) Project ID NULL
202 stop_operation (Stored procedure) Project ID NULL
300 validate_project (Stored procedure) Project ID Project name
301 validate_package (Stored procedure) Project ID Package name
1000 configure_catalog (Stored procedure) NULL NULL

[Executions]

[executions] has a one-to-one relationship with [operations], which means the [execution] table is a subset of the [operations] table. If you look at the ERD closely, the relationship from operation to execution is made only when the operation type is 200 – create_execution and start_execution (Stored procedures). This means only operation_type value 200 will generate a record in the execution table.

image

If you want to know which package in a project was executed, you can use the following query. This is the creation script of the [catalog].[executions] view.

SELECT     execs.[execution_id],
execs.[folder_name],
execs.[project_name],
execs.[package_name],
execs.[reference_id],
execs.[reference_type],
execs.[environment_folder_name],
execs.[environment_name],
execs.[project_lsn],
execs.[executed_as_sid],
execs.[executed_as_name],
execs.[use32bitruntime],
opers.[operation_type],
opers.[created_time],
opers.[object_type],
opers.[object_id],
opers.[status],
opers.[start_time],
opers.[end_time],
opers.[caller_sid],
opers.[caller_name],
opers.[process_id],
opers.[stopped_by_sid],
opers.[stopped_by_name],
opers.[operation_guid] as [dump_id],
opers.[server_name],
opers.[machine_name],
ossysinfos.[total_physical_memory_kb],
ossysinfos.[available_physical_memory_kb],
ossysinfos.[total_page_file_kb],
ossysinfos.[available_page_file_kb],
ossysinfos.[cpu_count]
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers
ON execs.[execution_id]= opers.[operation_id]
LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER(‘ssis_admin’) = 1)
OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)

Message related tables

If you want to check the execution log of a package, these are the tables that you need to query. [operation_messages] and [event_messages] have a one-to-one relationship similar to [operation] and [event]. [event_messages] is a subset of [operation_messages]. The below is the source code of the [catalog].[event_messages] view.

Declare @ExecutionID NVarChar(max)
select @ExecutionID=N’132′
SELECT     opmsg.[operation_message_id] as [event_message_id],
opmsg.[operation_id],
opmsg.[message_time],
opmsg.[message_type],
opmsg.[message_source_type],
opmsg.[message],
opmsg.[extended_info_id],
eventmsg.[package_name],
eventmsg.[event_name],
message_source_name =
CASE
WHEN (opmsg.message_source_type = 10) THEN ‘ISServerExec’
WHEN (opmsg.message_source_type = 20) THEN ‘Transact-SQL stored procedure’
ELSE eventmsg.message_source_name
END,
eventmsg.[message_source_id],
eventmsg.[subcomponent_name],
eventmsg.[package_path],
eventmsg.[execution_path],
eventmsg.[threadID],
eventmsg.[message_code]
FROM       [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE      opmsg.[operation_id] = @ExecutionID

If you execute an SSIS package deployed to a SSIS catalog and it fails, you will see a message similar to the following.

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  4:09:59 PM  Package execution on IS Server failed. Execution ID: 120092, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  4:09:59 PM  Finished: 4:10:27 PM  Elapsed:  28.641 seconds.  The package execution failed.  The step failed.

You will need to use either a prebuilt standard report or query the SSISDB tables to get a detailed error message. The following screenshot shows where you can locate that report. If you decide to query the SSISDB, you may want to consider using the SSIS Reporting Pack developed by Jamie Thomson.

image

Table definition

Here I list the tables and its definitions copied from BOL.

Schema

Table name

Definition

internal catalog_encryption_keys Contains SSISDB encryption key
internal catalog_properties Contains the properties of the Integration Services catalog.
internal data_type_mapping Contains SSIS data type and SQL data type mapping
internal environment_permissions
internal environment_references Contains the environment references for all projects in the Integration Services catalog.
internal environment_variables Contains the environment variable details for all environments in the Integration Services catalog.
internal environments Contains the environment details for all environments in the Integration Services catalog. Environments contain variables that can be referenced by Integration Services projects.
internal event_message_context
internal event_messages Contains information about all messages passed during events like OnWarning, OnError
internal executable_statistics Contains Detailed execution log for each executable
internal executables Contains Information about each executable object in packages
For example : Package itself, Execute SQL Task, ETC.
internal execution_component_phases Contains Detailed execution log for each data flow component.
internal execution_data_statistics Contains Number of rows sent through data flow and few other details
internal execution_data_taps Contains information about data taps
Data Taps are new features in SSIS 2012 which provides ability to log whole data stream in one of data flow’s data paths. and the best part of story is that you can add and remove data taps to a package at the time of execution
internal execution_parameter_values Contains the actual parameter values that are used by Integration Services packages during an instance of execution.
internal execution_property_override_values Contains information about property values that set at the time of execution of package and overrides default values.
internal executions Contains the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package. With every execution of package, an Execution_id will be assigned
internal extended_operation_info Contains extended information for all operations in the Integration Services catalog.
internal folder_permissions
internal folders Contains the folders in the Integration Services catalog.
internal object_parameters Contains the parameters for all packages and projects in the Integration Services catalog.
internal object_versions Contains the versions of objects in the Integration Services catalog. In this release, only versions of projects are supported in this view.
internal operation_messages Contains messages that are logged during operations in the Integration Services catalog.
internal operation_os_sys_info Contains system information like cpu count, available physical memory and total physical memory at the time of operation
internal operation_permissions Contains the effective permissions for the current principal for all objects in the Integration Services catalog.
internal operations Contains the details of all operations in the Integration Services catalog. Each operation can be applied to an object and have a status as a result.
internal packages Contains the details for all packages that appear in the Integration Services catalog.
internal project_permissions
internal projects Contains the details for all projects that appear in the Integration Services catalog.
internal validations Contains the details of all project and package validations in the Integration Services catalog.

Scripts

Code definition tables

Reza Rad shared a script that creates code conversion tables. I created and added a table for permsssion_type. You can download the script from SSISDB_CodeTables.

Posted in Deployment, SQL SERVER, SSIS | Tagged: , , , , | 1 Comment »

A Journey to DB deployment automation-Planning

Posted by kulmam92 on June 8, 2013

The mind map below shows what I had in mind when planning and implementing automation for DB Deployment. I will cover the details in a later series.

DB_Deployment_Xmind

Covered topics

Posted in Deployment, SQL SERVER | Tagged: , , , , , , , , , , | Leave a Comment »

Building SSIS 2012 using MSBuild – A Journey to DB deployment automation

Posted by kulmam92 on June 7, 2013

This post is the first in a series of entries I am planning to write detailing my experience while attempting to create a process in deploying a DB schema, SSIS and SSRS.

SQL Server Data Tools – Visual Studio Shell

SQL Server Data Tools (SSDT) is an extension of the environment provided by Visual Studio 2010 Shell.

VisualStudioShell_thumb

Wikipedia describes Visual Studio Shell

Visual Studio 2008 introduced the Visual Studio Shell that allows for development of a customized version of the IDE. The Visual Studio Shell defines a set of VSPackages that provide the functionality required in any IDE. On top of that, other packages can be added to customize the installation. The Isolated mode of the shell creates a new AppId where the packages are installed. These are to be started with a different executable. It is aimed for development of custom development environments, either for a specific language or a specific scenario. The Integrated mode installs the packages into the AppId of the Professional/Standard/Team System editions, so that the tools integrate into these editions.

The binary of the SQL Server Data Tools is devenv.exe typically found in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE.

image_thumb2

DevenvLocation_thumb1

SSIS Deployment model in 2012

To give an explanation as to why I decided to use the Project Deployment model with EncryptSensitiveWithPassword as the protection level, I will briefly describe it here. I will cover the topic at a greater depth in a later series.

Project Deployment Model

The 2012 version of Integration Services supports two deployment models: the Project Deployment model and the Package Deployment model. The Project Deployment model enables you to deploy your projects to the Integration Services server. The Project Deployment model has many benefits compared to the Package Deployment model. You can read Deployment of Projects and Packages and SSIS Package Deployment Model in SQL Server 2012 to get more information about the differences between the two deployment methods.

image_thumb5

If you build an SSIS project file using the project deployment model, a project deployment file will be created (.ispac extension). The project deployment file is a self-contained package that includes only essential information about the packages and parameters in the project. You can check it contents using a file archiver like 7zip.

image_thumb9

If you double click the created (.ispac) file, the Integration Service Deployment Wizard will be launched. The Integration Service Deployment Wizard has a command line interface (ISDeploymentWizard.exe).  You can get the created command in the summary screen.

image_thumb11

SSIS Catalog

The SSIS catalog is a new type of storage for SSIS. It’s a user database so you can query tables and easily backup/restore the whole repository. You may refer to SSIS Catalog and SSIS 2012 Configuration Guide – Part 1: Introduction for more information.

The SSISDB catalog is central to working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server.In an example case, you would need to set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.

image_thumb12

Security for SSIS Packages

To protect the data in an Integration Services package, you can set the protection level.

image_thumb13

Access Control for Sensitive Data in Packages give us an example of protection level settings based on the package life cycle.

Typically, you change the protection level as listed through the following steps as requirements change:

  1. During development, leave the protection level of packages set to the default value of EncryptSensitiveWithUserKey. This setting helps ensure that only the developer is able to see sensitive values in the package. You can also consider using EncryptAllWithUserKey, or DontSaveSensitive.
  2. When it is time to deploy your packages, the protection level should be changed to one that does not depend on the developer’s user key. Therefore you would typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team for the production environment.
  3. After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. Alternatively, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey orEncryptAllWithUserKey and using the local credentials of the account that will run the packages.

Command line build of SSIS packages

You may assume that MSBuild will be able to support an SSIS project file (..dtproj extension) by default, though this is not the case. You may see the following error message when attempting to build.

image_thumb15

DEVENV

As I have mentioned earlier, SSDT functionality can be utilized by running devenv.exe, which also provides access to a command line interface. You may refer to Devenv Command Line Switches for the available options. Building using devenv.exe works fine as long as your project’s protection level is not EncryptSensitiveWithPassword, or EncryptAllWithPassword. You can get devenv.exe by installing SQL Data Tools from the SQL2012.ISO. Just a note, The free SSDT tools from the web don’t have BI functionality. You can read Richard Fennell’s Getting SQL 2012 SSIS packages built on TFS 2012.2 for additional information.

D:\App_temp\SSIS\DeployDemo>”C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv” DeployDemo.sln /build “Development|Default”

Microsoft (R) Visual Studio Version 10.0.40219.1.
Copyright (C) Microsoft Corp. All rights reserved.
—— Build started: Project: DeployDemo, Configuration: Development ——
Build started: SQL Server Integration Services project: Incremental …
Starting project consistency check …
Project consistency check completed. The project is consistent.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\DeployDemo.dtproj’get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\Project.params’ get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\LEN-062_JY2012.nxa_repository.conmgr’ get updated.
Applied active configuration to ‘Project.params’.
Applied active configuration to ‘Package.dtsx’.
DeployDemo -> D:\App_temp\SSIS\DeployDemo\DeployDemo\bin\Development\DeployDemo.ispac
Build complete — 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

However, if you change the protection level to either EncryptSensitiveWithPassword or EncryptAllWithPassword, a window will pop up and ask you to enter the protection password. There’s no switch that allows you to provide a password from the command line.

image_thumb19

It was due to this issue that I explored using MSBuild for the build process.

MSBuild

It’s important to know what MSBuild is in order to understand how the new solution works. Let’s read the description from MSDN’s MSBuild page:

The Microsoft Build Engine is a platform for building applications. This engine, which is also known as MSBuild, provides an XML schema for a project file that controls how the build platform processes and builds software. Visual Studio uses MSBuild, but it doesn’t depend on Visual Studio. By invoking msbuild.exe on your project or solution file, you can orchestrate and build products in environments where Visual Studio isn’t installed.

Visual Studio uses MSBuild to load and build managed projects. The project files in Visual Studio (.csproj, .vbproj, .vcxproj, and others) contain MSBuild XML code that executes when you build a project by using the IDE. Visual Studio projects import all the necessary settings and build processes to do typical development work, but you can extend or modify them from within Visual Studio or by using an XML editor.

In short MSBuild uses an XML-based project file format to retrieve the necessary configuration:

MSBuild.exe MyProj.proj /property:Configuration=Debug

You may consider reading Walkthrough: Creating an MSBuild Project File from Scratch to understand more about the MSBuild Project file (AKA MSBuild script). The error message I got when I tried to build an SSIS project file (.dtproj extension) earlier indicated that the XML format of an SSIS project file is not recognized by MSBuild.

SSISMSBuild and MSBuild script

There’s a codeplex project (Microsoft SQL Server Community Samples: Integration Services) where you can create Microsoft.SqlServer.IntegrationServices.Build.dll. You can also get an MSBuild script that links Microsoft.SqlServer.IntegrationServices.Build.dll from here: msbuild script for building and deploying a SSIS .ispac file. Below is the MSBuild script from that link.

<?xml version="1.0" encoding="Windows-1252"?>
<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
          DefaultTargets="SSISBuild;SSISDeploy">
  <!--Requires a property called $(SSISProj) to be defined when this script is called-->
  <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISBuild" Condition="'$(SSISProj)' != ''">
    <PropertyGroup>
      <SSISProjPath>..\$(SSISProj)\$(SSISProj).dtproj</SSISProjPath>
    </PropertyGroup>
    <Message Text="**************Building SSIS project: $(SSISProjPath) for configuration: $(CONFIGURATION)**************" />
    <DeploymentFileCompilerTask
      InputProject="$(SSISProjPath)"
      Configuration="$(CONFIGURATION)"
      ProtectionLevel="DontSaveSensitive">
    </DeploymentFileCompilerTask>
  </Target>

  <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISDeploy" Condition="'$(SSISProj)' != ''">
    <Message Text="**************Publishing SSIS project: $(SSISProj) to: $(SSISServer) to folder: $(PROJECTNAME)**************" />
    <PropertyGroup>
      <ISPac>..\$(SSISProj)\bin\$(CONFIGURATION)\$(SSISProj).ispac</ISPac>
    </PropertyGroup>
    <DeployProjectToCatalogTask
          DeploymentFile="$(ISPac)"
          Instance="$(SSISServer)"
          Folder="$(PROJECTNAME)"
          CreateFolder="true"/>
  </Target>
</Project>

This MSBuild script performs two functions: SSISBuild and SSISDeploy. I saved the code to a file called “SSIS.MSBuild.proj” and executed MSBuild.exe illustrated below.

D:\App_temp\SSIS\DeployDemo\DeployDemo>MSBuild SSIS.MSBuild.proj /t:SSISBuild,SSISDeploy /p:SSISProj=”DeployDemo”,Configuration=”Development”,ProtectionLevel=”EncryptSensitiveWithPassword”,ProjectPassword=”1234″,SSISServer=”LEN-062\JY2012″,ProjectName=”DeployDemo”

image_thumb21

How to build Microsoft.SqlServer.IntegrationServices.Build.dll

I will explain how to compile Microsoft.SqlServer.IntegrationServices.Build.dll using the source code from the codeplex project (Microsoft SQL Server Community Samples: Integration Services). I’d like to mention the small challenges that I faced while trying to compile the source code.

  1. Prerequisites: The following should be installed on a machine that you are going to use.
    1. MS SQL Server 2012
    2. Visual studio 2010
  2. Download source code
    http://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest
    image_thumb23
  3. Unzip downloaded file
  4. Go to SSISMSBuild project folder (sqlsrvintegrationsrv-100610\main\SSISMSBuild).
    There are two projects under SSISMDBuild, 2008 and Project. Project is for 2012 is C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project
    SSIS Junkie mentioned SSISMSBuild is included in MSBuild extension pack. However, that integration is done only for a 2008 project.
  5. Reference hint path modification
    Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj from the text editor.image_thumb26Change the highlighted part to full path.image_thumb29
  6. Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj using visual studio.
    If you try to compile it, it will generate an error due to a missing key file. You can see the following line from the project file.
    image_thumb30
  7. Sign an AssemblyWith the project node selected in Solution Explorer, from the Project menu, click Properties (or right-click the project node in Solution Explorer, and click Properties). In the Project Designer, click the Signing tab. Then you will see the following screen.
  8. Choose new and provide file name and uncheck Protect my key file with a password.

    Check Signing an Assembly in Visual Studio for more details
  9. Compile
  10. Copy created Microsoft.SqlServer.IntegrationServices.Build.dll file to the following folder
    C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies

Summary

It took me quite a while to figure this out and I hope this can save you time when setting up an automated build process using MSBuild. I think it would be great if MSBuild adds support for SSIS projects by default in the future.

Posted in SQL SERVER, SSIS | Tagged: , , , , , , , , , | 44 Comments »