Monday, 17 December 2012

Case
I created a new environment in the Integration Services Catalogs, but I can't copy it. If I want a duplicate / clone the enviroment then I have to create it from scratch with all the variables. Where is the copy option?

Where is the copy environment option?

Solution
Well there isn't one yet (please see/vote this Microsoft Connect suggestion). So I copied the create_environment stored procedure and altered it to a copy_environment stored procedure. You have to know the environment id from the environment you want to copy. Double click the existing environment and get the number from the Identifier property.

Get identifier of existing environment

Add the new stored procedure and execute it like:

EXEC [catalog].[copy_environment] 3, 'Test', 'Test environment';

The result: a new environment with
same variables as 'Development'

PS you still have to reference the enviroment to your project manually:
Update: I adjusted the stored procedure and now it also links the new environment to the same project as the existing environment.

In this post I will eleborate SQL Server Configuration solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

Enable package configurations

2) Configuration Type
Select SQL Server in the Configuration type selectbox. Create a new Connection Managers that links to the database with your configuration table (1). Create a new Configuration table or select an existing one (2). Enter a name that will describe the configurations (3). After that click Next.

Configuration Type

3) Select Properties to Export
In this step you select all the properties you want to configure in the config table. In our case we select the ConnectionString property of both Connection Managers (but not the one from the new connection manager that you created in the previous step!). After that click next.

Select the two ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations.

Configuration name

5) Add Environment variable
We now have a third Connection Manager, but its connectionstring will be different on all servers in your dtap environment. So we need to configure that new Connection Manager as well. We will store its connectionstring in a Windows Environment Variable.
This step will vary for each Operating System. The screenshots are from my Windows 7 laptop. Go to the properties of "My Computer", then to Advanced system settings, then to the Advanced tab and then hit he Environment Variables button. Now add a new System variable with the name SsisConfigurationDatabase and the value must me the connectionstring of that new Connection Manager. Repeat this step for each ssis server in your dtap environment.

New System Variable

6) Add second configuration
Before we add a second configuration, you probably need to restart Visual Studio because it doesn't know your new Windows Environment variable. Add a new configuration and select Environment variable as Configuration type. Then select your new variable named SsisConfigurationDatabase. And click Next.

Configuration type

7) Select Target Property
Now select the connectionstring of the new connection manager and click Next.

Select the ConnectionString

8) Configuration name and move up
Give your configuration a suitable name and move it upwards so that it will be the first configuration. After that close the window.

Move it upwards!

9) Copy configuration database
Now copy the new configuration database table to all database servers in your dtap enviroment and make sure its values are adjusted to the server. Also make sure the environment variable is available on each server. Now you can deploy your package to each server and it will use its own configuration.

In this post I will eleborate the XML Config file solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

3) Select Properties to Export
In this step you select all the properties you want to configure in the config file. In our case we select the ConnectionString property of both Connection Managers. After that click next.

Select both ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations. After that hit the Finish button and close the configuration window.

Configuration name

5) View the file
If you browse to your config file and open it in for example Internet Explorer then you can see what's in it. Now you can just copy the xml config file to all environments. The path to the config file is embedded in the packages. So if you keep the file path the same, then you can just copy the packages and config files between environments. You can edit the config file in notepad to change the values for each environment.

6) Deployment Manifest
If you use different paths to store the config files then you should use the Deployment Manifest to change the embedded file path of the config file. Go to the properties of your project and go to the Deployment Utility page and select True at the CreateDeploymentUtility.

CreateDeploymentUtility

7) Build to create the Deployment Utility
Build your project (right click project in Solution Explorer and select Build). This will create the 3 files in the bin folder of your project:
﻿

Folder: \ProjectName\bin\Deployment\

8) Deployment
Now you can give these three files to the server administrator and let him/her double click on PackageConfig.SSISDeploymentManifest to deploy the package(s) and determine the config file folder. It's even possible to change the config value while deploying.

Sunday, 9 December 2012

Case
I have an Excel (xls) file as a source and it has cell protection turned on to prevent editing the wrong cells. It's an official form from a national care authority, so I can't change that. I don't even have the password.

The error doesn't occur when the file is still opened in Excel, but that's not really an option on a server.

Solution
You either have to remove the cell protection or convert the XLS file to XLSX (The ACE OLE DB provider doesn't have the same problem as the JET OLE DB provider). Also see/vote for this Microsoft Connect Bug report.

But can you do this automatically if you can't (or don't want to) ask that to the people who provided the excel files or if you have a whole bunch of those files? Here are a couple of options I have examined:

1) Interop Excel in Script Task
There is a Microsoft library (Microsoft.Office.Interop.Excel) available that can do that for you, but the BIG downside is that it requires an Office installation! Even if you download the Redistributable Primary Interop Assemblies (PIA), you still need Office installed. PIA is only a .Net wrapper that lets you communicate in .Net to the COM dll's from Office.
Serverside Office installation is a bad practice and Microsoft doesn't support/recommend an Office installation on a server.

2) Third party / open source dll's in Script Task
There are a lot of third party and open source dll's for excel. A .Net colleague of mine tried a whole bunch them, but they either don't work for the old (xls) excel files or they don't have an option to convert to xlsx or to remove the cell protection.

3) Cozyroc Excel Task
The COZYROC SSIS+ Library has an Excel Task that can do the conversion to XLSX for you. It's not for free, but I think it's worth it. Hiring an external BI/ETL consultant for two days is probably more expensive. Moreover you get a whole bunch of other cool tasks and transformations with it and you can test them freely within BIDS or SSDT before you buy them. This is my solution with the Cozyroc tasks.

(with a little help from Cozyroc Consultant Ivan Peev)

Note: Although the Cozyroc Excel Task works like a charm. I still think this is a bug that Microsoft should fix. Cell protection is an edit prevention and I 'm not editing...

Tip: You need to know the password to remove the cell protection in Excel. If you don't have it, use this vba macro to get it.