Can I deploy a single SSIS package from my project to the SSIS Catalog?

No. No you cannot. In SQL Server 2012, you will always deploy the entire .ispac file to the Catalog.

I’ve received this question a number of times in the past couple of weeks. It’s actually a feature we considered, and one that I (initially) argued for, as I know it is a common practice with SSIS solutions built for SQL Server 2005 and 2008. However, I was quickly convinced that the scenarios that require single package, or incremental deployment of a project can be solved using team development best practices and procedures. The central one being source control management of your SSIS packages.

Let’s compare developing an SSIS solution to developing a C#/.NET application.

  • A package (.dtsx) can be thought of a source code file (.cs)
  • The project (.ispac) can be thought of as the binary the source code compiles into (.exe)

If you have a team of developers working on the application, you would not do the following:

  • Place the source code in a common directory which all developers work on simultaneously
  • Update individual source files without recompiling the project binary
  • Commit partial changes to one source file when it could break code in other source files
  • Build and deploy the project to a test/integration/production environment when you are unsure whether the changes to other parts of the code made by other developers are complete

(Ok, maybe would not is too strong – I’ve definitely seen all of the above done before . How about we use should not instead?)

When I ask for details about the scenarios that people require single package updates for, it typically turns out that they are doing one or more of these “should not” things in their environments. If all of these things are red flags for application developers, then why do people do them with SSIS solutions?

I described some ideas I had for SSIS branching strategies when you’re using source control a while back. I’d like to add the following high level recommendations to that post:

  • If you have a team of developers working on SSIS packages, you should put you SSIS packages in source control
  • Developers should not commit changes until they are complete, or in at least in a reasonable state where the project is in a working state
  • If you have developer working on a very large change that could potentially break things (or one you want to do in multiple steps), do the work on a sub-branch to minimize impact to the rest of the team
  • Builds that get deployed to test/integration/production environments come from stable branches, not from the development branch

Now, there are probably cases where single package updates for an SSIS project deployment can come in handy (even when you’re using source control, producing regular builds off the integration branches, etc). I just haven’t been able to think of any. If you have one of these scenarios, I’d really like to hear it – either via Connect, or by posting here (or ideally, both). So far the customers I’ve worked with found that these deployment requirements went away once they started adopting some of these application development lifecycle best practices… but I’m always happy to be proved wrong!

Getting started with SQL Data Sync

Here’s the scenario – you have an on-prem SQL database that you’d like to expose to a number of internet based applications. This could be Windows Azure Reporting Services reports, web applications, Windows Azure based apps, or mobile clients… it’s might even be all of the above. Now, you could make SQL Azure your primary database location, however, chances are you have processes and business apps running on-prem that are accessing (and perhaps generating) the data within this database.

In comes SQL Data Sync – a web service in the Azure platform that allows one-way or bi-directional syncing of on-prem SQL databases, and SQL Azure databases. It also allows SQL Azure <-> SQL Azure syncing. This post provides a step-by-step look at how to get started with the service. For more information, you can also take a look at the Getting Started entry in Books Online.

[warning]SQL Data Sync is currently in a technology Preview state.[/warning]

[warning]This steps and user interface described in this post is current as of 2012-07-03.[/warning]

The first step to using SQL Data Sync is to provision yourself a server. Log into the Windows Azure Platform management portal, and click the Data Sync button on the left hand pane.

clip_image001

In the center of your window, you’ll see the Provision option.

clip_image002

Click on the Provision button to setup your service account. Select your subscription, and the region you’d like to host your hub.

clip_image003

Once you have provisioned the server, you will create your Sync Group.

clip_image004

Step 1 is to specify a name for the sync group. In this scenario, I’d like to sync an on-prem AdventureWorksDW database to the cloud, so I’ll name my sync group accordingly.

clip_image005

Once you’ve set the name, Step 2 is add a SQL Server database to the sync group.

clip_image006

Since this is the first Sync Group we are creating, we’ll be adding a new SQL Server database to the sync group. The sync direction specifies how the data is being moved:

  • Bi-Directional – data flows both ways between the Hub and the client
  • Sync to the Hub – data flows to the hub only. The hub acts as a mirror for changes made on the client.
  • Sync from the Hub – data flows to the client only. The client acts as a mirror for changes made to the Hub.

clip_image007

In this scenario, I’d like to mirror my on-prem database to make it available to Cloud applications, so I select “Sync to the Hub”.

On the next page, I need to select a Client Sync Agent. Since I don’t have one already installed, I select the “Install a new Agent” option.

clip_image008

I now download the agent binary to my local machine, enter a name, and generate an Agent Key.

Agent Configuration

Clicking the Download button brings you to a Microsoft.com download page, with the option to download and install the agent msi. During the installation process, you are prompted for an account which has network access. This becomes the account which will run the agent service.

clip_image009

Once the agent is installed, you will see a new “Microsoft SQL Data Sync” service created on your machine. Before you can start syncing, you will need to launch the Agent Configuration Tool (“Microsoft SQL Data Sync Agent Preview”).

clip_image010

Launching the newly installed Agent application brings up the configuration UI.

clip_image011

Back to the Windows Azure Management Portal to generate a key for the agent.

clip_image012

Copy the key from the management portal, and go back to the Agent configuration tool. Click the Submit Agent Key button and enter the key.

clip_image013

Now your Agent is linked to the Sync Group you are creating. We can proceed with making databases available.

clip_image014

Click Register to add a new database. You’ll enter the SQL instance information, and the database name. You have the option of using SQL Authentication (i.e. a user name and password), or to use Windows Authentication and connect to the database using the SQL Data Sync Agent service account.

clip_image015

Once a database has been configured, we can go back to the Management Portal.

On the next step, we will add a SQL Server database to our sync group.

clip_image016

Click the Get Database List button to retrieve the list of databases you registered with the SQL Data Sync Agent. Select your database (in this example I am selecting AdventureWorksDW).

Step 3 will be to configure a Windows Azure SQL Database to use as the Sync Hub.

clip_image017

Click the database icon to enter your Windows Azure SQL database information.

Note, for the Test connection to work, I had to enter:

  • Fully qualified server name (i.e. <server>.database.windows.net)
  • Database name (i.e. AdventureWorksDW_Sync)
  • Credentials
    • Username without @<server>
    • Password

Step 4 is to enter the configuration details.

clip_image018

For the configuration, you can specify the sync schedule, as well as the Conflict Resolution if you are doing Bi-directional syncing. In this example, I don’t expect to have any conflicts (as it’s a one way push to SQL Azure), but I’ll select Client Wins.

Step 5 is to define the dataset you will synchronize.

clip_image019

clip_image020

When we select our source database (in this case, the on-prem AdventureWorksDW database), we see that we have some tables that can’t be properly synced to SQL Azure. Clicking on the See the Report link brings up details.

clip_image021

For this example, we can ignore the tables that can’t be synced. Clicking Select All will automatically skip tables with incompatible schemas.

clip_image022

At the bottom of the UI, we can see a Row Filter area. Filtering allows us to sync only certain records… for example, we might want to only sync a single year of data from our fact table. This is essentially adding a WHERE clause to our sync query. To do this, we would select the table we want to filter, and click the Filter checkbox next to the column we are adding the WHERE clause on.

clip_image023

Step 6 is to “Deploy” the changes to the server.

clip_image024

Once you click Deploy, SQL Data Sync completes the configuration of the sync group. The selected schema will be created in your SQL Azure database, and the initial set of data will be moved over.

clip_image025

Your newly created Sync Group now appears underneath your Subscription on the left hand side of the Management Portal. On the right, you can see the dataset sync definition, including any filters you’ve defined.

clip_image026