Thursday, May 29, 2008

SSIS Configuration to Configuration to Configuration Schema

I've gotten several requests to put down in writing the configuration schema that I use as the base of my SQL Server Integration Services framework. It contains a set of configurations: an indirect environment variable configuration, which points to an XML configuration file configuration, which points to a SQL Server configuration. I learned this configuration from the Project REAL Reference Implementation. If you're getting started with a BI implementation, I highly recommend that you download it for some great ideas and best practices.

Steps to implement:

1) Create an environment variable on your machine with the name of SSIS_CONFIG_FILE and the value of: C:\SSIS\Config\MasterConfigFile.dtsConfig.

2) Create an SSIS configuration file at C:\ SSIS\Config\MasterConfigFile.dtsConfig with the line:
<configuration valuetype="String" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" configuredtype="Property"><configuredvalue><configuration configuredtype="Property" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" valuetype="String"><configuredvalue>Data Source=EnvironmentServer;Initial Catalog=SSIS;Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;</configuredvalue></configuration>
where “EnvironmentServer” is replaced with the appropriate server for that environment.

3) On the EnvironmentServers, create a table called SSIS.dbo.SSISConfigurations with all connection strings and values that you could want to use in your packages with the ConfigurationFilter "CommonConfigurations". You can create this table by opening up a package, creating a SQL Server configuration, and select the “New” button next to the Configuration Table option.

4) In your package, create an OLE DB connection manager named CONFIG_SERVER with dummy server and database information.

5) In your package, create an XML configuration file Package Configuration with the option selected “Configuration Location is stored in an environment variable” and select the environment variable SSIS_CONFIG_FILE. If the variable does not exist, close and reopen BIDS. Then (and make sure this is the second configuration entry), create a SQL Server Configuration that uses the CONFIG_SERVER connection manager and the “CommonConfigurations” filter.

6) For any new connection managers that you create, as long as you follow the naming convention of the connection managers that are already in the SSISConfigurations table, the connection string will automatically be loaded for use in your package!

All of the names and locations can of course be replaced by something more relevant to your servers. They can even be different on each server, just as long as the name of the environment variable is the same on all servers.

By using this schema, you reap many benefits. One perk is allowing you to move your packages through environments without having to change the package or the configuration file. You also are encapsulating all of your connection information in one database table, letting you secure private information. Finally, you are reducing the development time of your packages because by you don’t need to recreate the configurations to all servers; they will already be available for your package.

Version: SQL Server 2005 SP2

Monday, May 26, 2008

Calling all Girl Geeks!

I am, with GeekyCyberMom, putting together a Girl Geek Dinner group for the MD/VA/DC area. Girl Geek Dinners started in London by Sarah Blow and have cropped up all over the world. They are a way for women to get together in a welcoming environment to learn and discuss technology.

We are looking for sponsors and members. If you are interested in helping with or attending any of the events, catch up with us through here:

Blog
Wiki
Twitter

I know I'm looking forward to it!

Saturday, May 24, 2008

Delimited String Custom Data Flow Components on CodePlex

At my last client, Rushabh Mehta and I created some custom components to handle delimited strings in a dimension. Here is a description of the components. We have since cleaned up and released the code through CodePlex. You can download the code from the following sites:

SortDeDuplicateDelimitedString

UnPivotDelimitedString

Take a look at the projects and let me know if you have any comments, suggestions for improvement, or if you find them useful!

Tuesday, May 20, 2008

SQLTeach Toronto Wrap Up

After recovering from a serious lack of sleep from presenting at SQLTeach :), I wanted to jot down a couple of thoughts about the conference.

1) Jean-Rene Roy puts on an amazing conference. The DevTeach conference has been around since 2003, and this was the first year it was in Toronto.

2) The quality and experience of the speakers is of the highest quality, and since the conference is fairly small, attendees get a great experience with often individualized advice and attention.

3) I despise scaled evaluations. The scale used at this conference was as such: bad, average, good, excellent. While my presentations were rated fairly well, the problem is that this system promotes subjectivity under the guide of objectivity. Who is to say what a "good" versus an "excellent" presentation is? Are you a glass half empty or a glass half full type of person?

4) The conference contained a whole Agile track. I was fortunate enough to spend some time with these speakers learning about this exotic new land of acronyms. I am interested in looking more into how these concepts are applicable to developing Business Intelligence applications.

5) Who knew that talking for 2.5 hours straight would make me lose my voice? :)

Overall, I had a great time. I met some great people, both speakers and attendees. As always, I learned a lot, and hopefully I imparted some wisdom as well. Looking forward to the Montreal conference later this year!

Friday, May 16, 2008

NoVa CodeCamp 2008.01

I'll be speaking at the NoVa CodeCamp 2008.01 in Reston, VA tomorrow, May 17. The presentation will be "SSIS Tips & Tricks" - an advanced look at SQL Server Integration Services with a focus on increasing developer productivity.

I'm looking forward to the experience and meeting more of the developers in the area. If that's you, you should stop by!

Thursday, May 8, 2008

SSIS Custom Component Custom Property

When building an Integration Services custom component, you inherit from the class Microsoft.SqlServer.Dts.Pipeline.PipelineComponent. You can override any of the methods within this class to add your own functionality. If you want to add a custom property to the component, you will need to override the ProvideComponentProperties subroutine.

For example, if you want to add a custom property that will act as a switch for removing or including empty strings, you would use code similar to the following:

Dim RemoveEmptyStringsProperty As IDTSCustomProperty90
RemoveEmptyStringsProperty = ComponentMetaData.CustomPropertyCollection.[New]
RemoveEmptyStringsProperty.Name = "RemoveEmptyStrings"
RemoveEmptyStringsProperty.Description = "Boolean that determines if empty strings should be removed. Value should be True or False."
RemoveEmptyStringsProperty.Value = "False" 'Default value


Then in your execution logic (in the ProcessInput subroutine), you would use code similar to:

If ComponentMetaData.CustomPropertyCollection("RemoveEmptyStrings").Value.ToString.ToUpper = "TRUE" Then
    'Perform logic
End If


This will allow the custom property to show up on the initial Edit screen of the custom component. When creating a package, the developer can select the option appropriate to their business logic, and that option will decide the course of action at runtime.

Version: SQL Server 2005 SP2, VB.NET 2.0