Thursday, 13 February 2014

Case
I cannot access my Excel sheets in SSIS. The Excel version in the Connection Manager is Microsoft Excel 2007 (xlsx).

Could not retrieve the table information for the
connection manager 'Excel Connection Manager'.
Failed to connect to the source using the
connection manager 'Excel Connection Manager'

Solution
XLSX files don't use the out-of-the-box Microsoft.Jet.OLEDB provider, but they need the Microsoft.ACE.OLEDB provider. You either did not install it or you installed the 64bit version.

Download and install the 32bit version of the Microsoft Access Database Engine 2010 Redistributable. Because Visual Studio (SSDT/BIDS) is 32bit you can't use the 64bit provider for developing SSIS packages. If you already installed the 64bit version then you first need to remove it. You can't install 32bit and 64bit parts of office on the same machine. You will get an error when you run the installer (and you will get the same error if you have a 64bit version of Microsoft Office installed on your development machine):

You cannot install the 32-bit version of Microsoft
Access Database Engine 2010 because you currently
have 64-bit Office products installed. If you want to
install 32-bit Microsoft Access Database Engine 2010,
your first need to remove the 64-bit installation of
office products.

However this means that you can't run packages with Excel Connection Mangers in 64bit on your development machine. You need to switch to 32bit, otherwise you will get an error like:

Saturday, 1 February 2014

Case
Parameters and environments are a handy new feature in SSIS 2012, but creating environments with variables and referencing the environment to the project and the variables to the parameters is a lot of effort. No problem if you have to do it once, but if you deploy your packages through the DTAP servers you have to redo it on every single server. Is there a way to deploy environments?
﻿

No export option for Environments

Solution
Although it is easy to deploy packages through DTAP servers. Deploying environments is not possible. That could get a little tiresome especially if you have a lot of parameters in your project.

I have created a script/stored procedure that uses the project identifier as input, loops through the tables of the SSISDB and prints SQL-statements to execute SSISDB Stored Procedures.

How to get the project identifier

For every environment, variable and reference to this project it will generate creation scripts. You can copy these stored procedure calls, adjust the values where needed and execute them on the next environment.﻿﻿

Execute script then copy and execute output

Release notes

There are no checks or validations in this version. So it doesn't check whether objects already exist before calling the SSISDB Stored Procedures.

First deploy the SSIS project before executing the stored procedure calls on the next server.

Make sure the folder name is equal on the new server or change it in the stored procedure calls.

Make sure to check sensitive variables values (you can't get the value from the tables).

Here is how you call the stored procedure to generate the scripts. If you don't want to add the new stored procedure then you could just use the script inside the stored procedure.

exec catalog.deploy_environment 11

Add the following stored procedure to the SSISDB or use the TSQL code inside.