Archive for April 2007

Package Configuration Editor

Well, after a few weeks of playing around with it, I finally finished an initial version of a tool for editing SSIS configurations. It’s pretty basic, but it is functional. It supports editing configurations stored in XML files and databases. You can download it from here.

The initial version supports editing existing configuration sources. That is, you can’t create a new configuration file from scratch with it, but you can add new configurations, and edit and delete existing configurations. The nice part about this is that if you add a new configuration to an existing file, packages that use the configuration file will automatically make use of the new configuration, without you have to change the package at all. There is one caveat to this behavior, the package path value in the configuration has to be valid in the package that is using the configuration file. Otherwise, you will get a warning message when the package loads.

The same thing holds true for database configurations. If you add a new package path, but reuse an existing configuration filter, it will be applied to any packages that use that configuration filter. The database editor treats the configuration filter and package path as the key for a configuration entry, so you have to delete and re-insert a row to update either of those columns.

Some future improvements that I would like to make:

  • Add some validation to the values. Right now there is no checking to ensure the data being entered can successfully be converted to the appropriate data type.
  • Add a package browser for the package path.

I’ve tested this a bit myself, but I’m definitely interested in feedback on it, whether it is bugs or suggestions for improvement. You can leave comments on this post, on the file listing, or you can email me at jwelch@agilebi.com.

SSIS Presentation at CITPG

I presented an “SSIS 101″ session to one of our local user groups yesterday, the Carolina IT Professionals Group (http://www.carolinait.org/). It was an interesting presentation to do, as the audience is fairly broad, covering networking professionals, application developers, and hardware people. Out of the roughly 100 people in attendence, only seven had actually used SSIS, and there were two or three more that had used DTS. Over the past few years, most of my presentations have been done to audiences that are familiar with data warehousing, so it was a little different to present to a group that wasn’t ETL centric.

However, it was a good experience. The audience asked a lot of good questions. A number of people followed up after the presentation to say that they hadn’t been familiar with SSIS prior to the presentation, but now that they had seen it, they see a lot of use for it in automating some data movement tasks. One person told me that he currently spent one to two hours every morning babysitting a set of cursor based stored procedures that moved data from one server to another, and now he was going to automate the process with SSIS.

I’ve recently done a series of presentations around SSIS that focused on more advanced topics, and on some product development that Mariner (my employer) is doing around SSIS. I’ve been acting as a product manager on that, and it’s been a lot of fun. It’s given me the opportunity to apply a lot of the experiences I have had with SSIS toward creating something that will help companies manage their SSIS implementions over time. I promise not to turn this blog into a running advertisement, but as we get closer to launch I will include some links to the product information, as I’d love to get feedback from the community on it.

Retrieving Information from Active Directory with SSIS

I was recently asked to help someone retrieve information from Active Directory for use in SSIS. My initial thought was “This shouldn’t be too difficult. I know there is an OLE DB driver for Active Directory, so it should just be a matter of hooking up a few components.” As it turns out, it took a few hours of painful trial and error to get something working. Also, at the end of it all, I found a different approach that was as simple as I had originally envisioned it. Hopefully, this will save some time for the next person needing to do this.
I’ll start with a summary of the results, in case you don’t feel like wading through the rest. It does work, but there are a few caveats. You can use either an OLE DB connection manager, or an ADO.NET connection manager with the .NET OLE DB wrapper. I recommend the ADO.NET connection manager, as it will allow you to use access Active Directory as a source in your data flows. If you are using the OLE DB connection manager, you will be limited to using Execute SQL tasks and passing the recordset into the data flow. There is a sample package attached that shows both approaches.
I started out using an OLE DB connection manager and choose the OLE DB Provider for Microsoft Directory Services. I put in the server name, left the authentication as Windows Integrated, and clicked Test Connection. The test was successful, so I moved on to the next step.

I attempted to set up an OLE DB data source in my data flow, using my OLE DB connection manager. However, nothing worked. So I backed up, and tried an Execute SQL task. Still nothing. My first thought was that I didn’t have the syntax right for the query, but after trying it successfully outside of SSIS, I was looking at the connection again. Eventually I found a reference in MSDN to setting the ADSI Flag to 1 on the connection properties. After updating that, my Execute SQL task started working.

 The Execute SQL was set up to return the full resultset to an object variable. The query used was “SELECT cn FROM ‘LDAP://DC=DOMAINCONTROLLER,DC=local’ WHERE objectClass=’User’”. This retrieves all the users from Active Directory.

I then went back to using an OLE DB Source in the data flow, but it still wouldn’t work. The OLE DB Source didn’t seem like it was able to execute the query or get any metadata from the provider. So I took the approach of using a script source to process the recordset in the data flow.  (See this post for more information on handling recordsets in scripts.) That worked fine, but I was wasn’t completely satisfied.
I went back and tried creating a new connection manager. This time I used the ADO.NET connection manager, but still used the OLE DB Provider for Microsoft Directory Services through the .NET Provider for OLE DB. Just for grins, I didn’t bother setting the ADSI Flag on this connection.

I then created an ADO.NET Reader data source in my data flow, set the connection to the connection manager I just created and put the same LDAP query in it.

When I ran the updated package, it worked first time through (without the ADSI Flag=1). Since it’s been my understanding that it is always preferable to use the OLE DB connection manager when working with OLE DB providers, this behavior was a little frustrating. It seems like you should get the same behavior whether you are using the OLE DB connection manager or the ADO.NET connection manager with the OLE DB wrapper.
Anyway, that was my journey through SSIS with Active Directory. I’d definitely recommend using the ADO.NET connection manager, as it make the process much easier. Please check out the sample package attached if you have further questions.