SSIS Team Blog

SQL Server Data Tools – Business Intelligence for Visual Studio 2012 released online

Wee Hyong Tok — Thu, 07 Mar 2013 00:07:00 GMT

Today we are releasing SQL Server Data Tools – Business Intelligence for Visual Studio 2012 (SSDT BI) templates. With SSDT BI for Visual Studio 2012 you can develop and deploy SQL Server Business intelligence projects. Projects created in Visual Studio 2010 can be opened in Visual Studio 2012 and the other way around without upgrading or downgrading – it just works.

The download/install is named to ensure you get the SSDT templates that contain the Business Intelligence projects. The setup for these tools is now available from the web and can be downloaded in multiple languages right here: http://www.microsoft.com/download/details.aspx?id=36843

The setup includes:

Visual Studio 2012 integrated shell (if you don’t have Visual Studio 2012 on your machine)
Project templates for

Analysis services projects
Reporting services projects
Integration services projects

It will be installed as a new shared feature for SQL Server 2012 and can be uninstalled from there.

This download contains the same features as SQL Server 2012 SP1 and supports the themes introduced for Visual Studio 2012.

Some screenshots of the templates in Visual Studio 2012:

New project in Visual Studio 2012 black theme

Integration services project in Visual Studio 2012 dark theme

Multi-dimensional project in Visual Studio 2012 light theme

Tabular model project in Visual Studio 2012 dark theme

Reporting services project in Visual Studio 2012 light theme

Happy developing!

SSIS 2012: Creating a Simple ETL Package Tutorial

Carla Sabotta — Wed, 19 Dec 2012 00:42:00 GMT

There is now a SSIS 2012 version of the "Creating a Simple ETL Package Tutorial". The tutorial and lesson packages work with the AdventureWorksDW2012 database. And, the tutorial includes a new Lesson 6 that shows you how to convert a project to the project deployment model and replace configuration values with parameters.

The tutorial documentation is at SSIS Tutorial: Creating a Simple ETL Package.

To download the lesson packages and sample data for the tutorial, navigate to the DOWNLOADS tab of the Integration Services Product Samples project on CodePlex and click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file. To download the AdventureWorksDW2012 database, navigate to the Adventure Works for SQL Server 2012 release on the Database Product Samples project on CodePlex.

This samples readme provides additional information about running the lesson packages.

Deploy SSIS 2012 projects using catalog.deploy_project Stored Procedure

Carla Sabotta — Thu, 06 Dec 2012 19:44:00 GMT

In SQL Server 2012, you can use the catalog.deploy_project stored procedure to deploy an SSIS project to the SSIS server. You need to provide the binary contents of the project deployment file (.ispac extension), for the @project_stream parameter, along with the project name and the folder the project will be deployed to. The @project_stream parameter is varbinary(MAX).

You can retrieve the binary contents of the project file by using a SELECT statement with the OPENROWSET function and the BULK rowset provider. The provider enables you to read data from the file, returning the contents as a single-row, single-column rowset of type varbinary(max).

In the following example, the SSISPackages_ProjectDeployment project is deployed to the SSIS Packages folder on the SSIS server. The binary data is read from the project file (SSISPackage_ProjectDeployment.ispac) and is stored in the @ProjectBinary parameter of type varbinary(max). The @ProjectBinary parameter value is assigned to the @project_stream parameter.

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

For instructions and examples on how to use the catalog.create_execution, catalog.set_execution_parameter_value, and catalog.start_execution stored procedures to create, configure and start a package execution, see this topic in BOL.

Deploy and Execute SSIS Packages using Stored Procedures

Scheduling SSIS Packages and SSIS Catalog Reports -- Doc Updates

Carla Sabotta — Tue, 16 Oct 2012 21:31:00 GMT

I’ve recently updated the SSIS documentation in Books Online (BOL), to add some details around setting SQL Agent job options for packages stored in the SSIS catalog, as well as in MSDB and the file system.

I’ve also added some more information about the troubleshooting reports available for projects that are deployed to the new SSIS server.

You can find this additional information in the following BOL topics.

In researching these updates, I would like to thank the members of the Olympia Area SQL Server User Group and Tacoma SQL Server User Group, and Greg Larsen who is the chairperson for the Olympia user group and facilitates the Tacoma user group. Their input on the SSIS documentation and on the product contributed greatly to the documentation updates.

Important Note about Upgrading to SSIS 2012

Carla Sabotta — Fri, 21 Sep 2012 00:26:09 GMT

When you upgrade to SQL Server 2012 Integration Services (SSIS) on a machine that has SSIS 2005 or 2008 installed, SSIS 2012 is installed side-by-side with the earlier version. Unlike upgrades to previous versions, the upgrade process does NOT remove the SSIS 2005 or 2008 files, service, and tools.

In addition, with this side-by-side install, multiple versions of dtexec utility are installed. To ensure that you run the correct version of the utility, at the command prompt run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\<version>\DTS\Binn).

For more information about upgrading to 2012 SSIS, see Upgrade Integration Services and 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012.

SSIS Sessions at the 2012 PASS Summit

Matt Masson - MSFT — Wed, 19 Sep 2012 21:08:35 GMT

There are a whole bunch of SSIS sessions at the PASS Summit this year – I’m glad to see our product continues to be one of the most popular SQL topics around!

Pre-Conference

SSIS Design Patterns (BIA-299-P) - Andy Leonard, Jessica Moss, Matt Masson, Michelle Ufford, Tim Mitchell

Full day of SSIS training from the authors of the SSIS Design Patterns book.

Break out Sessions

10 Extraordinary Things to Achieve with Integration Services 2012 (BIA-322-S) - Peter Myers, Matthew Roche
Adapting Your ETL Solutions to Use SSIS 2012 (BIA-205) - Devin Knight
Become Less Fuzzy About Fuzzy Transformations! (LT-100) - Amy Lewis
Data Cleansing with SSIS 2012 (BIA-405-S) - Brian Knight
Enterprise Information Management: Bringing Together SSIS, DQS, and MDS (BIA-316-M) - Matthew Roche, Matt Masson
Extending SSIS 2012 Reports with Analytics (BIA-207-M) - Patrick LeBlanc
Real-Time Data Warehouse and Reporting Solutions (BIA-203) - Carlos Bossy
Slowly Changing Dimensions: An Integrated Approach (BIA-314) - Mark Stacey
Understanding the SSIS 2012 Deployment Model (LT-102) - Bill Fellows
Using SSIS 2012 for Data Warehouse ETL (BIA-319) - Thomas LeBlanc
Waiter, There's a Fly in My Data (BIA-101) - Rafael Salas
When ETL Goes Bad: Handling Errors and Data Anomalies in SSIS (BIA-209) - Tim Mitchell
Data in the Sky: Efficient On-Premise to Cloud Data Transfer (CLD-306-M) - Matt Masson, Wee Hyong Tok

Wow, I don’t know how I’m going to fit all these into my schedule…

Also note that there will be multiple people from the SSIS team at the summit, ready to answer any questions you have, or help with designing your ETL solutions. (It looks like I might have a couple of book signings scheduled as well – I’ll post details once they are available).

Hope to see you there!

SSIS with AlwaysOn

Matt Masson - MSFT — Wed, 19 Sep 2012 20:51:12 GMT

In SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. In addition, SSIS introduces new capabilities which allow customers with the ability to easily deploy to a centralized SSIS Catalog (i.e. SSISDB user database).

In this article, we explore how you can make use of SSIS and AlwaysOn Availability Groups. Using these capabilities, it allows you to provide high-availability of SSISDB and its contents (projects, packages, execution logs, etc). We also discuss how to deal with patching with service packs.

Important Note: Enabling AlwaysOn with SSISDB does create some complications when you install a cumulative update or service pack which modifies the SSISDB schema. SSISDB patches are installed when the SQL Server instance first starts up, but putting a database into an availability group causes it to be in an OFFLINE state on startup, which will cause the patching process to fail. To address this, you MUST first remove SSISDB from the availability databases, then patch each node, then recreate the availability group.

If the above warning hasn’t scared you away, keep reading!

SSISDB in Availability Groups

To use SSIS with AlwaysOn, you’ll need to add the SSIS Catalog (SSISDB) into an Availability Group. You’ll need to do the following steps:

Make sure you meet the prerequisites for using AlwaysOn

Connect to every node and create the SSISDB catalog. We need to create the catalog even on secondary nodes to create the other server-level objects (cleanup jobs, keys, accounts etc) that are used by SSIS.

Delete the SSISDB databases on secondary nodes.

Create an availability group, specifying SSISDB as the user database

Specify secondary replicas.

Failover

The primary reason to use SSIS with AlwaysOn is to leverage the failover capabilities. When a failover occurs, one of your secondary nodes automatically becomes the new primary node.

Unfortunately, when this happens with SSIS, you will get an error (15581) when the system tries to run an package on the new primary node:

“Please create a master key in the database or open the master key in the session before performing this operation”.

This occurs because of the way SSISDB encrypts sensitive information (such as connection string passwords, and certain parameters. To resolve this, the database master key needs to be re-encrypted by the service master key after failover occurs. To resolve it, you’d need to re-encrypt the database master key by running this T-SQL script:

USE SSISDB　　　　　　 
OPEN master Key decryption by password = 'x' -- Password used when creating SSISDB
ALTER Master Key ADD encryption by Service Master Key

Once the key has been re-encrypted, the new primary node will be able to run SSIS packages.

Auto-Detecting Failover

Having to manually run a script every time failover occurs isn’t ideal. Unfortunately, AlwaysOn doesn’t expose any events we can plug into. To work around this, you can setup a SQL Agent job that polls the replica status, and detects when its node has become the new primary.

Step #1: We create a table containing with a single row in a database other than SSISDB (say, X):

USE X;
CREATE TABLE [dbo].[replica_role](
[replica_role] [tinyint] NULL
)
INSERT INTO [dbo].[replica_role] ([replica_role]) (
    SELECT [role] 
    FROM sys.dm_hadr_availability_replica_states
    WHERE is_local = 1
);

Step #2: On every node, create an agent job (ex, “ssisdb_failover”) with a recurring step that runs below T-SQL script every certain amount of time (say, every 2 minutes):

USE X;
 
DECLARE @last_role TINYINT;
SET @last_role = (
   SELECT TOP 1 [replica_role]
   FROM [dbo].[replica_role]
);
 
DECLARE @current_role TINYINT;
SET @current_role = (
   SELECT ROLE 
   FROM sys.dm_hadr_availability_replica_states 
   WHERE is_local = 1
);
 
IF (@last_role = 2 AND @current_role = 1) -- Last time it was secondary, 
                                          -- currently it is primary: need re-encrypt 
                                          -- the database master key
BEGIN
    USE SSISDB;
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x' -- Should be encrypted! See below
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
 
USE X;
UPDATE dbo.[replica_role] SET [replica_role] = @current_role;

Note, in the above script, the password for the database master key is in plain text. As a security best practice, you can store it in a table using a simple symmetric key encryption, and retrieve it later. For example:

USE X;
-- Add a column in which to store the encrypted data.
ALTER TABLE dbo.replica_role
ADD ssisdb_pwd VARBINARY(128); 
GO
 
--If there is no master key for X, create one now. 
IF NOT EXISTS 
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
 CREATE MASTER KEY ENCRYPTION BY 
 PASSWORD = 'xxx' -- Your master key password
GO
 
CREATE CERTIFICATE ssisdb_cert
WITH SUBJECT = 'SSISDB Password';
GO
 
CREATE SYMMETRIC KEY ssisdb_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ssisdb_cert;
GO
 
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY ssisdb_key
DECRYPTION BY CERTIFICATE ssisdb_cert;
 
-- Encrypt the value in the column using the symmetric key.
UPDATE dbo.replica_role
SET ssisdb_pwd = EncryptByKey(Key_GUID('ssisdb_key'), N'x');
GO

To retrieve the password, replace the line “OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'” in the agent job script with below lines:

USE X;
OPEN SYMMETRIC KEY ssisdb_key DECRYPTION BY CERTIFICATE ssisdb_cert; 
DECLARE @pwd NVARCHAR(MAX);
SET @pwd = (SELECT TOP 1 CONVERT(NVARCHAR,DecryptByKey(ssisdb_pwd)) FROM dbo.replica_role);
DECLARE @sqlString NVARCHAR(1024);
SET @sqlString = 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''' + @pwd +'''';
USE SSISDB;
EXECUTE sp_executesql @sqlString;

Patching with Service Packs

SQL Server service packs usually run in single-user mode, while an availability database must be a multi-user database. Therefore, during installing a service pack, you may find all availability databases including SSISDB will be taken OFFLINE and thus fail to patch. To address this, you can first remove SSISDB from the availability databases, then patch each node (Failover Cluster Instances have to be in the same patch level), then add SSISDB back.

Other Considerations

When an unplanned failover occurs, it is important that the status of SSIS package executions reflect the most up-to-date state. You can restart the instance so that a default SSIS cleanup job will be executed to fix the status of any packages/ deployments that were running at the time the instance went down. The status will be changed to Terminated; temporary data created during project deployment will be removed.

Note that your SSIS packages will still need to have their own failover logic in them. This article provides a good starting point for creating highly available packages.

This post was originally written by Ke Yang and posted back in June – we took the article down when we found the issue with patching SSISDB databases while they are in an availability group. We’ve since updated and re-posted the information!

Video about CDC for Oracle Databases in SSIS 2012

Carla Sabotta — Tue, 10 Jul 2012 22:59:48 GMT

This video presentation Rakesh Parida (MSFT) demonstrates how to perform CDC for Oracle databases using SSIS 2012. You'll learn how the CDC features work, how to create and administer the CDC service, as well as how to use logs and traces to troubleshoot the service.

The video is availble online at http://technet.microsoft.com/en-us/sqlserver/jj218898.aspx .

For more information about the CDC for Oracle, see the CDC for Oracle in SQL Server 2012 blog post by Rakesh.

Balanced Data Distributor (BDD) for SQL Server 2012 is Now Available

Matt Masson - MSFT — Wed, 20 Jun 2012 12:50:04 GMT

The Balanced Data Distributor (BDD) transform has updated and released for SQL Server 2012.

The BDD transform makes it easy to split your data flow up on a buffer-by-buffer basis. The two primary scenarios for it are when you want to do parallel inserts into a destination…

… and when you want to parallel work across multiple paths …

More information can be found on the links below:

Migrating SQL Server 2000 Data Transformation Services (DTS) Packages to SQL Server 2012

Carla Sabotta — Fri, 04 May 2012 18:34:00 GMT

SQL Server Integration Services (SSIS) 2012 doesn't support migrating or running DTS packages. You do have the following options for migrating your DTS packages to SSIS 2012.

Migrate the packages to SSIS 2005 or SSIS 2008, and then upgrade the packages to SSIS 2012. For information about migrating packages to 2005 or 2008, see Migrating Data Transformation Services Packages (2005) and Migrating Data Transformation Services Packages (2008) in Books Online.

Recreate the SQL Server 2000 Integration Services packages by using SSIS 2012. The What's New in Books Online provides an overview of the new features in SSIS 2012. The Integration Services (SSIS) Packages topic describes the elements of an SSIS package.

Here is a list of the DTS functionality that has been discontinued in SSIS 2012.

DTS runtime
DTS API
Package Migration Wizard for migrating DTS packages to the next version of
Integration Services
Support for DTS package maintenance in SQL Server Management Studio
Execute DTS 2000 Package task
Upgrade Advisor scan of DTS packages.