This author’s blogs now continued elsewhere

July 12, 2013

Hello All:

It has been over two years since I have posted anything to this blog and when I finally got back to it, I noticed a few encouraging comments left by readers. To tell you the truth, I was a little surprised to learn that people were still reading the content. I have since changed jobs and am working for Blum Shapiro, the largest regional accounting, tax and business consulting firm based in New England with offices in West Hartford and Shelton, CT, Quincy, MA and Providence RI. As part of that firm, I can now post under their blog site found here: http://blog.blumshapiro.com/. And here’s the great thing: I’m not the only one authoring post for the Blum Shapiro blog site. We have a very competent staff of technology consultants with expertise in SharePoint, C#, VB Dot Net, SQL Server, Business Intelligence and Analytics, Master Data Services, and much more. I have recently posted an article titled Dynamic Cell Formatting in SSRS. I chose this topic because it was fairly basic, and I needed something where I could concentrate more on the tools of the blog, rather than the content itself.

If you have been following this blog and come across this message, please know that I won’t be adding any new content to it under this site, but under the Blum Shapiro blog site. Come check it out! I hope to continue with SQL Server related topics in the near future.

Advertisements

Multiple Tabs in an Excel Source

August 21, 2009

Here is a bit of SSIS design that some users may find helpfull. Suppose you have an Excel file with multiple tabs, all with the same data. Or, you get a new monthly file each month, but the file name and/or the Tab name is different each time.

Using a ForEach loop, you can loop over all the (Excel) files in your folder, and using the write up in my post about looping over flat files, you can get to each one programatically inside SSIS. This will take care of the situation where the file name changes each month; your loop will execute only once.

But what if you a) have multiple tabs in your Excel file, or b) don’t know in advance what the name(s) of the tab(s) will be. You need some way to examine the Excel file in advance of the Data Flow and get the tab name(s).

I’m going to simplify this scenario and assume we are working with only one file, with multiple tabs. If you have multiple files to work with, simply encase everything here in a For Each loop configured to handle the multiple files.

Start by creating a Variable named TabList, of type Object. This will hold the list of tab names. Add a Data Flow, and in it drag in a Script Task. When prompted, set it as a Source. On the Inputs and Outputs tab, click on the Output 0 and add a Column called TabName, of type string[ST_STR]. In the script itself enter the following code under the CreateNewOutputRows sub:

Dim excelFile As String = “Data Source=” + Me.Variables.FullFileName.ToString +  “;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;”      
     

It’s fairly simple code: Open an Excel File as an OLE DB Connection then use the GetSchema(“TABLES”) method to get the list of tables in the file. Finally, send each row of data (containing a tab name) to the Source component’s Output buffer.
 
Note that I have a bit of logical testing for the currentTable variable. In Excel, tab names end with a ‘$’ as in Sheet1$. And you may also get a tab name of “Sheet1$FilterDatabase”, referencing the exact same range of data. But since we only want one copy of the data, we check to see if the tab name ends in ‘$’ and if it does, use it.
 
But if your Tab Name contains spaces, then the whole name will be enclosed in single quotes, including the $ delinitor, as in ” ‘My Tab Name with spaces$’ “. (Sort of like SQL Server and encasing object names with [  ] . So that means the $ is NOT the last character of the tab name, but the second to last.
 
Moving on, now that we have a Source for the Tab Names on the Data Flow, send it to a RecordSet destination. On the Component Properties tab, specify a the User::TabList variable to hold the data. Then on the Input Columns tab, select the TabName column.
 
Back on your Control Flow, downstream of the Data Flow, add a For Each loop. Set it for Foreach ADO Enumerator on the Colletion tab, and select User::TabList as the ADO Source Object. Leave the selection below it as Rows in the first table.
On the Variable Mappings tab, create a variable User::TabName, type string to hold the Tab Name.
 
Inside the loop, add your Data Flow task. On it, in the Source connection, set the Data Access Mode as “Table name or view name variable”, then in the Variable name, select User::TabName.
 
 

 

 

 

 

 

  

 

 

 

 

 

 
 

 

Dim cn As OleDbConnection = New OleDbConnection(excelFile)      
cn.Open()
Dim tablesInFile As DataTable = cn.GetSchema(“TABLES”)      
Dim tableCount As Integer = tablesInFile.Rows.Count     
Dim currentTable As String
Dim columnRestrictions(3) As String  

For Each tableInFile As DataRow In tablesInFile.Rows        
 currentTable = tableInFile(“TABLE_NAME”)    
 If Len(currentTable) = InStr(currentTable, “$”) Or _                      
           Len(currentTable) = InStr(currentTable, “$”) + 1 Then
                 Output0Buffer.AddRow()
                 Output0Buffer.TabName = currentTable

            End If
        Next
    

cn.Close()   
Output0Buffer.SetEndOfRowset()

 I’ll leave it to you to edit the first line of code to get the proper Connection String for your particular case.


Creating Links to Other Reports

July 28, 2009

When designing a Reporting Services (2005 or 2008) report and you want to have a link to another report, there is an option under the Text Box Properties for “Go to report”, but when the link is executed, the resulting browser page is a bit hard to figure out how to get back to the parent report.

A better solution is the other Action item  “Go to URL”, With it you can set up an expression for the url that will be executed. While you can hard-code the entire URL, it is best to use parameters and global fields to make the coding ‘maintenance free’ when moving from TEST server to Production. An example of a URL expression is shown below:

 

= “javascript:void(window.open(‘” +

Globals!ReportServerUrl + “?” + Globals!ReportFolder +

“%2f<My Report Name here>&ClientReportID=” + Cstr(Fields!ClientReportID.Value)

+ “‘,’_blank’))”

 

The string is explained line by line:

  • First and last line make it so the link is executed in a popup window. Basically, it ‘wraps’ the string with a java command that opens a new browser window.
  • Second line: Uses two Global Built-in Fields for server and folder path. Note the inclusion of the literal “?” between the two.
  • Third Line: Start with the literal “%2f” followed by your hard-coded linked report name. Add parameters as needed.

 

NOTES:

  1. This link method cannot be tested in your Visual Studio Development environment because the two Global fields are out of context and are empty. Test it once it gets deployed to the TEST server. After all, that’s what it’s for!
  2. Use the Globals.ReportFolder field only if the linked report will be deployed to the same location (virtual folder) as the source report. If not, you may need to hard-code the folder path and hope for the best when it is deployed.
  3. Replace the spaces in literal names with plus signs and slash characters with “%2f”:  “Extranet+Reports%2fMy+Report+Name”
  4. There is a whole host of other command that can be concatenated to the string, like for hiding the parameter selection section, jumping to a Document Map entry, etc.

Using Excel as SSIS Data Source

June 23, 2009

This blog post will contain some of the lessons I have learned using Microsoft Excel as a source file for SSIS. It is still very much a work in progress.

The first thing I learned is that the Excel Connection Manager is somewhat limited, and that to get any real flexibility out of it, you need to use the OLE Db connector for Microsoft Jet 4.0. But that’s only good for the earlier version of Excel (up to version 2003), and you have to do some manual editing of the Connection String properties.

Try this: Right click in the Connection Manager try and select New OLE DB Connection. Click the New button and select Microsoft Jet 4.0 OLE DB Provider (from the Native OLE DB  list). Click the Browse button to locate your Excel spreadsheet. Before closing this dialog, click th All page (on the left side) and scroll all the way to the top. Next to Extended Properties, type the following: Excel 8.0.

 

<more to come>


Looping Over Flat File Sources in SSIS

November 4, 2008

Introduction

It seems I am often helping people configure SSIS packages to loop over several flat files to extract data to be sent to a single SQL destination table. While some may have found a way to accomplish this in the old DTS, the For Each loop in SSIS 2005 makes this much easier. I hope that this post will get you started on this powerful task.

DISCLAIMER: Please take the time to understand everything that is going on with the package you design from this blog. Fortunately, it does not contain any blocks of code that you can copy and paste into your desinger to make it all work for you. I say “Fortunately” because it is fortunate for me. If it did contain such code, I would also be obliged to warn you about blindly copying and pasting code from the internet into your application without fully understanding what it does.

The Scenario

One of the most common scenarios is when data comes in from various scources in flat file format (.txt, .csv, or some other format) and needs to be loaded into SQL Server. It is fairly easy to set up a single  flat file source in SSIS, but there are a couple of extra things to watch for when trying to loop over several of them.

Start with a single file and a working Data Flow

The first thing to do is create a Flat File Connection Manager to be used as the Source. Point it to ONE FILE in the folder that contains ALL the flat files you will be using. Configure the rest of the Data Flow to use this one Flat File. When designing packages such as this, I usually start small and build from there, getting the one file to work first, making sure the column mappings on the destination are correct ext, before adding the Looping container.

Add the Loop:

After you have a working Data Flow for one file, drag a ForEach loop onto the Control Flow. Then drag and drop your working Data Flow task into it. For the loop container, set the Enumerator (on the Collections page) to “Foreach File Enumerator”. Choose your Folder and select to retrieve Fully qualifield file names. (See below, even if the screen shot is fuzzy)

foreach

Click the Variable Mappings page. Create a mapping to a new variable which we’ll call FileName (with a Data Type of String). Note that the Index for this should be 0 (zero). That’s about all you need for this task.

The Flat File Connection Manager

The last thing to do is configure the Flat File Connection Manager to make it smart enough to switch to the next file in synch with the loop. Highlight it in the Connection Managers tray, then find Expressions in the Properties pane. Create a new Expression here. Select ConnectionString for the property, and set it equal to the FileName variable.

Run Time:

Here’s what’s going to happen at RunTime: The For Each loop will look at the first file in the folder specified and load the fully qualified name into the FileName Variable. The Flat File Connection Manager will then use that string to connect to the appropriate file in the folder.

Bells and Whistles

That’s really all there is to get this to work. However, as is typical, there are lots of other bells and whistles you can add to help manage your files. One common task is to have the completed files moved to a different folder after they have been processed so that when NEXT month’s data arrives, you won’t get collisions.


Saving Passwords in SSIS Packages

June 30, 2008

Browsing the Microsoft TechNet forums under SQL Server Integration Services, I constantly find posts from users with a common thread: “Why won’t my package run once deployed to the server”. A lot of the issues have to do with the saving of passwords in the Connection Managers. This narrative may help to shed some light on what is going on, and what you can do to fix it.

 DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does.

Connection Strings

Every time you add a Connection Manager (whether from a Shared Data Source or not), it has a Connection String associated with it. The connection string may be as complex as the example below: 

Data Source=Server_Name;User ID=MyUser;Password=AbC123xYz;Initial Catalog=Db_Name;Provider=SQLNCLI.1;Persist Security Info=True;

or it may be as simple as a path to a text file: “C:\My_Folder\Text_Data.txt”

In any case, the information stored in the Connection String tells SSIS how and where to connect to get the data you want. In many cases, ‘non SQL’ databases such as Oracle, AS400, or other legacy systems may require a user name and password because those systems do not support (Windows) integrated security.

Passwords at Design Time

So you, as a SSIS designer, dutifully configure your SSIS Data Sources and Connection Managers to save usernames and passwords because, like all of us, you don’t want to keep inputting the password every time you go to test run the package. AND, you make the common assumption (like I did early in my SSIS days) that by clicking the box that says, “Save my password”, the password you supply will magicaly persist with the life of the package, no matter where it goes or where is is deployed.

Saving a Connection String Password

Such is not always the case. The password will stay with the package, but only under certain conditions.

Package Protection Levels

One of the Package properties is one called Package Protection Level, the default of which is “Encrypt Senstive With user Key”. (Note that I have added the spaces to the names and settings for clarity and readability here.) This default setting basically says this: “SSIS will encrypt passwords and as long as YOU are the one running the package, then SSIS will know how to DECRYPT those passwords because it is the same USER who saved them.” The trouble comes when you deploy this package (with its saved passwords) to the server, and it gets run under the context of some other user.

If you just look at the setting choices above, it is easy to see that there is no really good choices, as “Encrypt All With Password” or “Encrypt Sensitive With Password” means that you need to supply a password in order to save, deploy, and run the package. And since we have enough passwords floating around already, nobody wants to start another set just for their SSIS Packages. So that leaves many users to try “Server Storage” but get an error.

The other question that may not have been asked yet is, “What happens if the password changes? Do I need to re-design and re-deploy all my packages?”

The answer lies in the dialog box presented to the user when executing the package from within SSIS or using the DTExec or DTExecUI command, or when scheduling a packag to run via SQL Server Agent. Each of these GUI’s has a tab titled “Data Sources” which lists the Connection Managers and their Connection String WITHOUT any saved password info, regardless of the Protection Level you used when saving the package. The thing to do is check off any Connection Manager in this list that requires a password and manually edit the Connection String to include it.

Additional Disclaimer

Some kind readers of this blog have responded with the comment that when following these steps above, the SQL Agent did not save the changes they made in the DataSources tab. I do remember seeing some similarly strange bahavior a while back, but was never able to pinpoint the cause. I am certainly not discounting those responders who made the claims. I can only offer that I THINK you will have a better chance of success if you do the edits to the DataSources as the LAST thing you do before saving and closing the dialog box. If any reader can verify or discount these suggestions, I would love to hear from them.

Since I wrote the above disclaimer, I came across a post on the SSIS MS Technet forum. The author said that when using Configurations, the external Configuration will override any Connection String edits done in the dialog box shown above. This may explain the behavior a little more.

Advanced Steps

If you want to take this a step further, review my blog at

https://toddchitt.wordpress.com/2008/06/27/SSIS_configs

This will walk you through the steps to create a Configuration Database in SQL Server that will eliminate the need to manually edit DataSource entries for every job you create.

Good luck, and happy coding!

Todd


Using Various Configuration Settings in SQL Server 2005 Integration Services

June 27, 2008

Introduction

I frequently watch the Microsoft TechNet forums for questions regarding SQL Server 2005 Integration Services. One theme that continually crops up, either in the questions themselves, or the answers given, relates to the use of Configurations in SSIS packages to make the packages easier to maintain as they migrate from Development, to Test, and finally, to Production environments. The methodology I describe here has worked for me in the past and hopefully will work for you as well.

DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does!

A Basic Understanding of Environments

With the release of SQL Server 2005, Microsoft made a shift to a more traditional setup regarding the whole DTS/SSIS design environment. Gone are the days when the DBA, or anyone else with proper access, could open a DTS package, edit it (and possibly break it), save it, THEN decide to test it, only to find that: 1) it doesn’t work and 2) the changes cannot be reverted. What IT Manager in their right mind would let a C# or VB.NET coder edit a piece of code in a production environment without that change being fully tested and proven? Such WAS the case with SQL Server 2000 and DTS design environment. With SSIS, you now have the ability to work on your package in a development environment, test it fully, then, once satisfied, send the finished copy off to the test environment, and finally to production.

The Problem

So, you ask, how does one ‘switch’ their package from one environment to the next? Some people out there are going through the arduous task of manually editing each and every connection manager in each and every package when it gets moved from one environment to the next. I recently developed a series of packages that had 50 child packages, each with several Connection Managers. Yes, Shared Data Sources made things a little easier because all the packages used the same set of Data Sources, but there has to be a better way. And with SSIS Configurations, alas, there is.

A Basic Understanding of Configurations

With Configurations, your package has the ability to get settings and values from places EXTERNAL to the package itself. The list of those ‘external places’ includes, amoung others, Registry entries and Environment Variables.  But for this discussion, I will be focusing only on two options: XML Files and SQL Server entries. Open an SSIS Package and right-click on the Control Flow, then select Package Configurations. Check the box Enable package configurations. Familiarize yourself with this dialog box. We’ll be visiting it quite a bit.

Basic Overview

What I’m going to walk you through in this document is a way for a package to first visit an XML Configuration document to retrieve the location of a SQL Server database. This database will hold all the connection string information for all the Connection Managers that the package uses, along with other inportant settings like Variable values, etc. There will be one database per environment (Dev, Test, Prod) running on different instances of SQL Server (which in most cases is different physical servers).

Let’s Get Started

Before you get too far ahead, we need to do some setup work. Open SQL Server Management Studio and connect to your Development server. Create a new database called SSIS_Configuration. (See script below.)

CREATE DATABASE [SSIS_Configuration] ON PRIMARY
NAME = N’SSIS_Configuration’,
FILENAME = ‘D:\MSSQL\SSIS_Configuration.mdf’ ,
SIZE = 10240KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON ( NAME = N’SSIS_Configuration_log’,
FILENAME = N’D:\MSSQL\SSIS_Configuration_log.ldf’ ,
SIZE = 1024KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10 %)
GO

 Note: You may need to edit the script to place the mdf and ldf files in their proper place for your environment.

There, now that wasn’t too bad was it? Let’s keep going and add a table:

USE [SSIS_Configuration]
GO 

CREATE TABLE [dbo].[Package_Config](
–[Application_Name] [varchar](50) NULL,
–[Common_Name] [varchar](50) NULL,
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]

 Notice the first two columns are commented out. The four remaining columns absolutely need to be in this table in order for SSIS to retrieve the info. I just added the other two to mine to give me a better handle of managing the numerous rows that will be saved there. If you want, remove the comments from the first two lines, and/or add an IDENTITY column.

We don’t need to add any data to this table directly. Our first SSIS Package is going to do that for us.

NOTE: This table can be generated from within an SSIS Package, but you will only get the four basic required columns. If you wanted more, you would need to manually edit the table design to add them.

Using Shared Data Sources

In the Business Intelligence Development Studio (BIDS), open the Project/Solution of your SSIS Packages. Under Shared Data Sources, create a new SQL Server Connection to the SSIS_Configuration database above. Make sure you use SQL Native Client as the Provider.

In one of the packages, right-click in the Connection Manager tray and select “New Connection from Data Source”. Choose the SSIS_Configuration connection just added.

NOTE: I design ALL my packages with Shared Data Sources stored at the Package level.

Creating The XML Configuration

Open the Package Configuration Organizer dialog box (remeber that one?) and click “Add”. Select XML as the type, then under the file name, click Browse. Create a new folder on the C:\ drive (this will get saved on the workstation, I named mine C:\SSIS_Config\) and specify a file name. My full path and filename is as follows:

C:\SSIS_Config\SSISConfigDb.dtsConfig

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of the Connection Manager for the SSIS_Configuration database as shown below.

Click Next. Give the Configuration a name that makes sense. Example: “SSIS Config ConnStr”. Configuration names must be unique inside a package, but they are for reference purposes only.

Back in the Package Configuration Organizer, click the Add button again. But this time, select the following options:

  • For Configuration type: “SQL Server”
  • For Connection: “SSIS Configurations” (or whatever you named the Connection Manager for your SSIS_Configuration database)
  • For Configuration Table: “[dbo].[Package_Config]” (choose from the list based on what you named your table)
  • For Configuration Filter, enter something like “Connection String for <MyDatabase>”

NOTE: If you are having trouble accessing the database and/or table you created earlier, you may need to click the “NEW” button as appropriate to generate the CREATE sql statement.

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of ONE other Connection Manager in the package. Give the Configuration a name. I typicaly use the same name as the Filter from the above step.

Repeat this process for the other Connection Managers in the Package. NOTE: DO NOT fall to the temptation of having only ONE Configuration ‘Filter’ with MULTIPLE Connection String properties associated with it. MAKE SURE you create a NEW ‘Filter’ for each and every Connection String/Connection Manager. (I’ll explain why later!) When finished, close the dialog box.

Just for the heck of it, open your Windows Explorer and look for the XML file you created on your C:\ drive. Open it in NotePad, Explorer, or some other XML editor. See if the tags and values jibe with what you set up. Now open SSMS and query the one table in your SSIS_Configuration database. Look at the various fields and values. If you added the two extended columns like I did, you can manually edit them to suit your needs, but DO NOT TOUCH any other fields. I typically use “Global Connection String” in the Application_Name field and the name of the database for the Common_Name field.

When the package runs, here is what is going to happen: First, the SSIS Run-Time engine will read the settings in the XML file and determine where the SSIS Configuration database is located (because you saved the Connection String property of the SSIS_Configuration database there). Then, knowing that, it will go to that server, database and table and get the Connection String property of all the other Connection Managers in the package, in the order specified in the Configurations dialog box. Only once that is done, will it start moving through your package, executing the tasks and moving data in the Data Flow.

If you now manually edit one of the ConnectionString properties in your SSIS_Configuration database, the next time a package runs that uses that Configuration entry, it will override the design-time ConnectionString with the ConnectionString from the SSIS_Configuration database.

So far, this is not earth shattering, but stick with it for a little longer.

Reusing Configuration Settings in Other Packages

 On subsequent packages, add the Connection Managers from Shared Data Sources in the same way. Then add Configurations for each, starting with the XML entry for SSIS_Configuration database. BUT, when you add each one, the system will determine that the file (or database, table, and filter) already exists. When it does, you will get a message like displayed below.

 Resue Existing Connection

Choose “Reuse Existing” and this will ‘hook’ the Connection String settings of the Connection Managers to the values stored (in the file or table, as appropriate). DO NOT select “Overwrite!”

A good practice would be to create a template package with, as a minimum, the SSIS_Configuration Connection Manager and its Configuration already in place. Then use the template to create regular packages.

One of the keys to making this work across all packages is to use the same ConnectionManager name  for a connection to the same database. (Shared Data Sources takes care of this for you, which is why I like them so much). If you name one Connection Manager as “Customer Contact DB” in one package, and as “Contact info for Customers” in another, you are going to have trouble. Since, in my work place, the database name is the same in Development, Test, and Production environments, I usually use the name of the Database for the name of the Connection Manager. Keeps things simple and consistent.

Migrating to Test and Production

You should be at a point where all the packages you have are set up with Configurations, and they all look for an XML file on the C:\ drive FIRST, then from there look to a SQL Server database to get the rest of the Connection Strings. So how do you move them to the next environment? And here’s the great thing about this method: You don’t need to edit the packages at all! Follow these basic steps:

  1. Copy the XML Config file from your local C:\ drive to the exact same folder location on the TEST server.
  2. Open the XML file in an editor and manually edit the Server Name portion of the tag to reflect the name of the TEST server (or, more accurately, the name of the SQL Server instance that hosts your test environment).
  3. In the SQL Server instance for the TEST Environment, create the SSIS_Configuration database as before.
  4. Create the table with the exact same meta data.
  5. Copy all rows from the Development table to the table on the TEST server.
  6. Manually edit the Connection String properties (stored in the  “ConfiguredValue” field) to reflect where each database is located in the TEST Environment. If you are lucky, you may only need to edit the “ServerName=xxxzzzyyy” portion of the Connection String.

Now install all your packages on the TEST Server. When they go to run, they will look for the xml file (on the TESTserver’s C:\ drive), figure out that the SSIS_Configuration Database is on the TEST server, and go there to get the location (Connection String) of all the Connection Managers in the package.

Follow the same steps to create the table and database in your Production Environment, making sure the XML file is placed in the same location, and edited as needed.

One Filter per Connection Manager

Why one ‘filter’ per Connection? Suppose you have 4 standard database Connections Managers that you use and create your one SQL Configuration ‘filter’ to save all 4 Connection Strings (resulting in 4 rows of data in the table, but all with the same Filter value). Suppose the NEXT package you create only needs 3 of those 4. When you connect to that filter and choose “Resue Existing” it is going to still hook the three Connection Managers’ Connection String property to the Configuration, BUT when it tries to RUN the package, it will try to configure 4 Connection Managers, but only find 3.  One-for-one, in my opinion, is the way to go here.

Configurations Are Not Just For Connection Strings

You are not limited in saving Connection String information in a Configuration table. I typically also save things like Variable Values as well. I recently created a package that needed to loop over all Excel files in a folder, extract data then move each file after processing. I saved the Folder location in a Configuration because in Dev, I had the files in a folder under My Documents, but in Production, the location was a file server. The Configuration entry for the Path saved me from having to re-design the package to run in Production.

Another typical use is for deciding who should recieve the email if and when a package fails. I create many of my packages with an Event Handler set up at the root package level, and on the OnTaskFailed event. I only include a Send Mail Task, and name it “Failure Email”. I then set up a Configuration entry to save the ToLine property of the “Failure Email” task as a configured value. The ‘package path’ is relative, so it is always the same in every package (does not depend on the package name) and the task name is the same for all packages. So now all I do is edit the Configured Value in each of the three databases, and voila, the package will send an email to a different person (or persons) if it fails in a different environment.

Developer’s Tip: Running against TEST or Prod from your Workstation

There were times when I needed to run my package in the Production environment to verify 100% that it would work, but did not want to (or could not) deploy it to the server and test it there. I was able to set up additional SSIS_Config.dtsConfig files on my C:\ drive, each one pointing to a different server. Since the files all need to be named the same, I created three folders under C:\SSIS_Config\ called DEV\, Test\, and Prod\. Each folder contains an exact copy of the XML file with the one value edited as appropriate. Now, if I need to run my package against Production, I simply COPY (don’t move) from the Prod\ folder and overwrite the one in the location that SSIS will look at. Then when I test run the package from withing BIDS, it runs against Production.

NOTE: If you do this, make sure you 1) keep track of what the ‘current config’ is in your XML file and 2) change it back to DEV when finished.

Passwords in your Config Db

Many times, the connection to a database is NOT through integrated security (as much as Microsoft would want us to use), but through a user name and password. When you go to save a Connection String property of this type to the SSIS_Configuration database, you may notice that the password does NOT get saved with the string. This is by design, and not for the scope of this write-up. Just know that you need to go back an manually edit the ConfiguredValue column of the entry in question and add “Password=abcd1234;” in the string.

Overview

Hopefully, you were able to follow these instructions, even thought the screen shots are a little blurry. It took a while to settle on this methodology, and it came from watching a lot of my initial packages fail, reading a lot of error messages, and deleting and recreating Configuration entries of various types before settling on the system laid out here. But it works. Hopefully, it will work for you as well.

If you have issues with attempting to copy these steps, don’t be afraid to post a comment back to the blog. I’ll get notified and make a personal response if warranted. I also monitor the MS TechNet forum for Integration Services found here: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.integrationsvcs

Good luck, and happy coding!