Saturday 22 March 2014

Package Configurations with BIML

Case
I want to use SSIS Package configurations in my BIML script. How do I do that?

Solution
Here are a couple of examples of the most used package configurations. Screens are from SSIS 2012 package deployment, but it works the same in SSIS 2008.

Environment Variable Config
I have one Connection Manager named Meta and I added package configuration to get its connectionstring from a Windows Environment Variable. That variable already exists and contains a connectionstring. The screens are what the BIML script below will produce.
Environment Variable Config


















<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Task with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    
   </Tasks>
  </Package>
 </Packages>
</Biml>
Download

SQL Server Configuration
I have a second Connection Manager named Source and I added package configuration to get its value from a SQL Server configuration table. This configuration table is stored in the Meta database. Note: the configurations should already exist in that table
SQL Server Config


















<Biml xmlns="http://schemas.varigence.com/biml.xsd">

 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
  <!-- My Connection Manager to a source database -->
  <OleDbConnection
   Name="Source"
   ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>

 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

    <!-- SQL Server Configuration -->
    <!-- The configuration table should already contain values -->

    <!-- ConnectionName is the name of the connection manager containing the configuration table -->
    <!-- Name is for both the Configuration Filter in the database table and the name in the Package Configurations Organizer window -->
    <PackageConfiguration
     ConnectionName="Meta"
     Name="SourceConfiguration">
     <!-- Table contains the name of the configuration table -->
     <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
    </PackageConfiguration>
    
   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy 1"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    <ExecuteSQL
     Name="SQL - Dummy 2"
     ConnectionName="Source"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>
Download
The combination of these two configuration types is often used in a DTAP street.

Parent Package Variable Configuration
I have a variable that is filled by a variable from the parent package. This is done with Parent Package Variable Configuration. In BIML script you will find this in the variable tag and not in the configurations tag!
Parent Package Variable Config



















<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <!-- Parent Package Variable Configuration -->
   <!-- Note: this is not in the Configurations tag, but in the variable tag -->

   <!-- InheritFromPackageParentConfigurationString is for both the name of the parent package variable -->
   <!-- and the name in the Package Configurations Organizer window-->
   <Variables>
    <Variable
     DataType="String"
     Name="MyChildPackageVariable"
     InheritFromPackageParentConfigurationString="MyParentPackageVariable"
     Namespace="User">SSISJoost</Variable>
   </Variables>

  </Package>
 </Packages>
</Biml>
Download

XML Configuration File
I have a Connection Manager and I have an XML configuration file to configure its connectionstring. The xml/dtsConfig file already exists with the correct values otherwise the package won't work.
XML Configuration File


















<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
  <OleDbConnection
   Name="Destination"
   ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- XML Configuration File -->

    <!-- The name is for the name in the Package Configurations Organizer window-->
    <PackageConfiguration Name="Destination Configuration">
     <!-- ExternalFilePath is the path of the config file  -->
     <ExternalFileInput
      ExternalFilePath="D:\DestinationConfigurations.dtsConfig">
     </ExternalFileInput>
     <ConfigurationValues>
      <!-- You can leave the value property empty -->
      <!-- The value of the PropertyPath should also be in the DtsConfig file -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectionStringDestination"
       PropertyPath="\Package.Connections[Destination].Properties[ConnectionString]"
       Value=""
       >
      </ConfigurationValue>
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Destination"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>
Download
This XML configuration type can also be used in a DTAP street.

Saturday 1 March 2014

SSIS 2012 with Team Foundation Server - Part I

Case
Team Foundation Server (TFS) is a handy tool when you work with multiple people on the same visual studio project. You can check out the files you work on and you still have the previous versions if you mess up. How do you get TFS working for SSIS 2012?

Solution
SSIS 2012 uses SQL Server Data Tools for Visual Studio 2010 for development, but you can also use Visual Studio 2012. They are called Visual Studio 2010/2012 Shell. Both have a different version of Team Explorer:

Visual Studio 2010: Microsoft Visual Studio Team Explorer 2010
Visual Studio 2012: Team Explorer for Microsoft Visual Studio 2012

This blog post shows how to install Team Explorer and shows how to setup Visual Studio to use TFS for SSIS. I have tested these for TFS 2010 and 2012. The SQL Server version that I used was Developer Edition 11.0.2100.60 RTM and VS2010 Shell 10.0.40219 SP1 and VS2012 Shell 11.0.50727.1 RTM. For SSIS 2008 (R2) and VS2008 Shell see this earlier blogpost. If your boss didn't purchase TFS then you could also get a free online TFS workspace.

In Part I covers:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS

In Part II you will read:
D) Adjusting development process




A) Install Team Explorer for Visual Studio 2010
Use the 2010 download link above. It's an ISO file. Mount or extract the iso file and execute the setup file. Next follow the setup screens. There are no real options to customize the installation: Next, Accept, Next, Install, Finish.
























Reinstall Service Pack 1
If you have Service Pack 1 installed for Visual Studio 2010, then you have to reinstall SP1 when you finish installing Team Explorer. Else you could get an error like this when starting SSDT or SSMS:
Only some of the Microsoft Visual Studio 2010 products
on this computer have been upgraded to Service Pack 1.
None will work correctly until all have been upgraded.
















You can download 'Microsoft Visual Studio 2010 Service Pack 1 (Installer)' here: http://www.microsoft.com/en-us/download/details.aspx?id=23691


B) Install Team Explorer for Visual Studio 2012
Use the 2012 download link above. It's an ISO file or an exe. Execute the setup file. Next follow the setup screens. There are no options to customize the installation: Accept, Install and launch VS2012.






























C) Setup Visual Studio to use TFS
For both versions of Visual Studio setting up TFS is the same.
In the Team-menu choose Connect to TFS





















If you have updated Visual Studio 2012, then this first step will open the Team Explorer pane on the right side. There you can click on the Connect link to open the window in the next screenshot.

Click on Servers-button to add a TFS server

Click on Add-button to add a TFS server

Add the tfs URL and choose between http and https

Click the OK-button and wait

Enter your credentials

Click the Close-button

Select the TFS project


Now you have the Team Explorer pane available




















































































































































In the second part you read what's next.


SSIS 2012 with Team Foundation Server - Part II

Case
I have installed Team Explorer and setup Visual Studio to use it. What's next?

Solution
In Part I you read:
A) Install Team Explorer for Visual Studio 2010
B) Install Team Explorer for Visual Studio 2012
C) Setup Visual Studio to use TFS

This second part covers:
D) Adjusting development process



D) Adjusting development process
Because you can now work with multiple developers on the same project, you have to make some arrangements with your fellow developers, like:

1) Get latest version project
Get the latest version of the project on a regular basis. Otherwise you will miss new packages, project connection managers and project parameters. Do this for example each morning or before you start developing. There is also an option in Visual Studio to automatically get the latest version of the solution when opening it.
Get everything when a solution or project is opened.

















2) Get latest version package
Get the latest version of a package before editing it. There is also an option in Visual Studio to automatically get the latest version of a package when checking it out.
Get latest version of item on check out.

















3) Adding new package to project
When you add a new package to the project, the project self will be checked out. First first rename the new package, save it and then check in the project and the new (empty/clean) package. Otherwise your fellow developers cannot change project properties or add new packages.
Adding new package will check out the project























4) Disable multiple check out
Working together on the same file at the same time is nearly impossible, because it's hard to merge the XML of two versions of a package. Therefore you should disable multiple check out in TFS or check out your package exclusively (not the default in TFS).
In Team-menu click Team Project Settings, Source Control

Uncheck the multiple checkout box






































5) Don't check in faulty packages
Try not to check in package that doesn't work. Especially when you work with the project deployment model, with which you can only deploy the complete project.
Don't check in faulty packages



















6) No large/complex packages
Don’t make packages to large/complex. Divide the functionality over multiple smaller packages, because you can’t work with multiple developers on the same large package at the same time.

7) Sensitive data
The default Package Protection Level is EncryptSensitiveWithUserKey. This will encrypt passwords and other sensitive data in the package with the username of the developer. Because your colleagues will probably have different usernames they can't edit or execute packages that you made without re-entering all sensitive package data.
The easiest way to overcome this, is to use DontSaveSensitive as Package Protection Level in combination with Package Configurations. Then all the sensitive data will be stored in the configuration table or file and when you open the package all this data will be retrieved from the configuration table or file.
If you're using the Project Deployment Model in combination with sensitive parameters instead of Package Configuration, then the easiest workaround is to use EncryptAllWithPassword or EncryptSensitiveWithPassword with a password that is known within the developmentteam.

8) Development standards
When you're developing with multiple people (or someone else is going to maintain your work) then it's good to have some Development Best Practices like using prefixes for tasks and transformations or using templates. This makes it easier to transfer work and to collaborate as a team.

9) Comments
When you check in a package, it's very useful to add a meaningful description of the change. This makes it easier to track history.
Check in comments

















10) Branching, Labeling and building
Beside versioning and checking in/out packages there are more interesting functions in TFS that are probably more common in C# and VB.Net programming, but worth checking out. Here are some interesting links about TFS and SSIS:

 

 



Related Posts Plugin for WordPress, Blogger...