Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘SSIS’ Category

SSIS Trace File Source Adapter

Posted by sqlwiseguy on May 4, 2009

I just found out about the Trace File Source Adapter available from SQLIS.com.  This tool will allow you to read a SQL 2005/2008 trace file within an SSIS package.  I learned about it from this entry on Chris Webb’s blog.

I have not used or even downloaded it yet, but I will as I am interested in all things Trace/Profiler related.  It looks interesting although I don’t see a compelling use for it at this point.  I am firm believer in being aware of the tools available and this is another one to add to the toolbox.

Posted in SQL Server, SSIS, Tools | Leave a Comment »

Deploying Custom SSIS Components

Posted by sqlwiseguy on April 9, 2009

This post is really for me.  I have written 1 custom component for SSIS 2005 and wrote an article on it for SQLServerCentral that was recently re-published.  In the discussion someone asked for specific steps for installing and using it.  Well, since it has been about a year, I couldn’t remember all the specifics so I’m blogging the steps so I have place to go the next time I need it.  So on with the steps:

  1. Register the component in the GAC (Global Assembly Cache).  There are 2 ways to this:
    • Drag and drop the component into C:\Windows\Assembly
    • Use the GAC utility that that is part of the Visual Studio SDK and the Windows SDK.  Here is the syntax:
        gacutil.exe  /if Path\AssemblyName
    • Depending on which one you installed it will be located in one (or both) of these locations if you used the default install:
      • C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin
      • C:\Program Files\Microsoft SDKs\Windows\v6.0\Bin
  2. On your development machine put the component in one of the folders under C:\Program Files\Microsoft SQL Server\90\DTS (default install location):
    • \Connections
    • \ForEachEnumerators
    • \LogProviders
    • \PipelineComponents
    • \Tasks

The component I created is a Pipeline Component so it belongs in the PiplelineComponents folder.  Once you have put it here you can add it to your toolbox by right-clicking on the toolbox, selecting “Choose Items”, select the SSIS Data Flow Items tab (in this case), and select the Error Details component.

This is really a summarization of this blog post by Matthew Roche that you can read for more details.

Posted in Development, Personal, Published Articles, SQL Server, SqlServerCentral, SSIS, Tools | Leave a Comment »

Error Handling in SSIS re-featured on SQLServerCentral

Posted by sqlwiseguy on April 3, 2009

I am honored that my first ever published article, Error Handling in SSIS, was re-featured on SQLServerCentral today.  I don’t know how Steve selects articles to feature a second time, but I assume it was because they were popular.  Here are a couple of earlier blog posts about this article/subject:

Error Handling in SSIS Published

SSIS Error Logging Custom Component

Posted in Development, Professional Development, Programming, SQL Server, SqlServerCentral, SSIS | Leave a Comment »

SSIS Variables and the Script Component

Posted by sqlwiseguy on March 3, 2009

About 2 weeks ago, yeah I know, why wait 2 weeks to blog on something, I was working with a script component in SSIS and I thought I needed to use some Package or Dataflow level variables in order to accomplish the task before me. Key words in that last sentence, “I thought”. They are key, not because it shows I actually thought about what I was doing, but because I had thought incorrectly. Not exactly a new thing for me either, but I digress. The task I was trying, and eventually was successful at, was taking a delimited string of ministries or job titles, and breaking them out into individual rows for each ministry while also creating a column on which to group. The format of the source data is something like this:

PersonNo Location StartDate EndDate  Ministries
-------- -------- --------- ------- ----------
12346 A 08/01/92 08/31/92 Teacher
12346 A 09
/01/92 12/14/92 Teacher, Language Consultant
12346 A 12
/15/92 12/31/95 Teacher, Language Consultant, Administrator
12346 A 01
/01/96 05/31/96 Teacher, Language Consultant, Administrator, Bookkeeper
12346 A 06
/01/96 01/06/08 Bookkeeeper

The desired output of the script component is:

PersonNo   Location  StartDate  EndDate    Ministry            Groups
---------- --------- ---------- ---------- ---------- -------
12346 A 08/01/92 08/31/92 Teacher 1
12346 A 09
/01/92 12/14/92 Teacher 1
12346 A 09
/01/92 12/14/92 Language Consultant 2
12346 A 12
/15/92 12/31/95 Teacher 3
12346 A 12
/15/92 12/31/95 Language Consultant 4
12346 A 12
/15/92 12/31/95 Administrator 5
12346 A 01
/01/96 05/31/96 Teacher 6
12346 A 01
/01/96 05/31/96 Language Consultant 7
12346 A 01
/01/96 05/31/96 Administrator 8
12346 A 01
/01/96 05/31/96 Bookkeeper 9
12346 A 06
/01/96 01/06/08 Bookkeeper 9

The desired final output of the Dataflow is this:

PersonNo Location  StartDate EndDate  Ministry
-------- --------- --------- -------- ----------
12346 A 08/01/92 12/14/92 Teacher
12346 A 09
/01/92 12/14/92 Language Consultant
12346 A 12
/15/92 12/31/95 Teacher
12346 A 12
/15/92 12/31/95 Language Consultant
12346 A 12
/15/92 12/31/95 Administrator
12346 A 01
/01/96 05/31/96 Teacher
12346 A 01
/01/96 05/31/96 Language Consultant
12346 A 01
/01/96 05/31/96 Administrator
12346 A 01
/01/96 01/06/08 Bookkeeper

In order to get the Groups I needed to be able to compare the current row to the previous row. In a script component you only have access to the current row, so I assumed (yes, I know what that means), that I would need dataflow scoped variables to contain the pertinent values from the previous row. I created the needed variables and then entered them in the ReadWrite Variables property of the script component to simplify access to them. Well, then when I ran the dataflow it failed at the script component with the error:

The collection of variables locked for read and write access is not available outside of PostExecute

The issue with this is that PostExecute only runs after ALL the rows have been processed which did not meet my needs as I could not modify them when the values changed. So I googled the error message trying to find out how to get around the error. First, I decided to remove the variables from the ReadWrite property of the dataflow. Then I used the variable dispenser to access the variables. Hey, believe it or not this works! You can unlock the variables for read and write using the variable dispenser and make the modifications. I thought I had solved my problem. Then over the weekend I realized that since the PostExecute function only runs after ALL the rows have been processed, this means that the Script Component is created once within the dataflow. So instead of needing the variables to be scoped to the dataflow, I only needed class level variables within the script component. Lesson learned.

The only reason you would need variables scoped outside the the script component is if you need the data OUTSIDE the script component.

The great part about the whole process was that I learned how to use an asyncronous script component and more details about how the script component works.

Posted in SQL Server, SSIS | Leave a Comment »

SSIS Error Logging Custom Component

Posted by sqlwiseguy on August 19, 2008

Well, I finally got around to writing my first custom component for SSIS. It is a synchronous transform component for Error Logging in a DataFlow. I was using a script component, which I wrote about for SQLServerCentral, that I was copying and pasting all over the place and I finally had had enough. Much of the code in the component is based on Josh Twist’s XMLify Custom Component which I found through Jamie Thompson’s excellent SSIS Junkie Blog. I also used the examples in Professional SQL Server 2005 Integration Services, to get through creating the component.

The code itself is pretty easy to follow, if you know anything about SSIS programming ;). It just accepts the input columns (ErrorCode is required, all others optional) and creates the new columns I want to pass out which are:

  1. ErrorDesc – retrieved using the ErrorCode passed in.
  2. ErrorTask – the Data Flow which hosts the component.
  3. ErrorStep – the name of the Error Details component. If anyone knows how to get the name of the previous component let me know as that is really what I’d like to have here.
  4. PackageTime – the start time of the containing package.
  5. ErrorDetails – an xml document consisting of name-value pairs based on the input columns.

Probably one of the neatest things I learned was how to debug the component. I had never used the Debug -> Attach to Process feature of Visual Studio before. It was really cool to start an SSIS Package with a breakpoint in it (never done that before either), then in my Custom Component solution attach to the SSIS process (DtsDebugHost.exe), continue the SSIS package, and then hit breakpoints in my component code. Man, do I LOVE learning new things!

Feel free to use the component and improve on it. I make no warranties on it.

I have posted the code and component in zip format on my new SkyDrive account (link at the bottom of the post). I had to create the SkyDrive account because I can’t put attachments on the blog. The code is also attached to the discussion of the article on SQLServerCentral, here.

Posted in SSIS | 5 Comments »

SSIS Webinars, tablediff, and Free Zoom Tool

Posted by sqlwiseguy on July 19, 2008

I had the opportunity to attend 3 of 5 free webinars on SSIS presented by Brian Knight of Pragmatic Works. The 2 I chose not to participate in were introductory in nature, and I have some experience with SSIS. I attended Administering and Deploying SSIS Packages, Performance Tuning in SSIS, and SSIS for the DBA. I enjoyed each of the webinars and picked up some good tips. I enjoy taking any opportunities, particularly free ones, to expand my toolkit and knowledge base. I appreciate Brain and co-workers taking the time to present the webinars as I know that there had to be a lot of time and effort put into doing them. I hope it pays off for them in their business as well. I believe that the goodwill it will. Check out the Pragmatic Works web site as they offer some very good custom SSIS tasks for free! They also offer some other commercial product as well.

I actually used something I learned in one of the Webinars today, Webinar SSIS for the DBA,. I used an Execute SQL Task to populate an object variable with a list of schemas and tables and then used a For Each Loop Task to loop through the result set and use the Execute Process Task to run the tablediff utility Microsoft has included with SQL Server 2005 (I read about it in Benjamin Wright-Jones’ blog earlier today). It is a command line utility that compares 1 set of tables (source and destination) and I wanted to compare all the tables in my database to my development database so I needed the loop. Tablediff compares the schemas and if they are different will return an error, “can not compare the table x to table y because the schemas are different”. If the schemas are the same it will, depending on the parameters you supply, show any differences in the data and prepare a T-SQL scipt to synchronize the data. I actually was only interested in comparing the schemas and, not having read all the documentation, was hoping it would report those differences as well. Maybe in a later version of SQL Server a schema comparison tool will be included (sorry Red Gate). It was still a good exercise, and knowing about the tablediff utility will probably come in handy later.

One last thing, Brian used a nice free tool to zoom and markup during his presentation. It is called ZoomIt and is by Sysinternals (now part of Microsoft). I would recommend it to anyone who give presentations as it really helped us see what Brian was doing.

Posted in Professional Development, Speaking, SSIS, Tools | 3 Comments »

SSIS SCript Component Rant

Posted by sqlwiseguy on May 2, 2008

I certainly don’t consider myself an SSIS expert, but I have done some work with it. It is a very powerful tool, but some things bother me about it. The first thing is the inability to debug Script Components in a data flow. I can set breakpoints in a Script Task, but in a Script Component I am left with message boxes and logging. This is a very painful way to try to debug code. Anyone with classic ASP experience can relate (response.write, anyone?). Microsoft went to the trouble of including debugging capabilities in ASP.NET and I certainly hope it is added to Script Components in SSIS in 2008.

Another annoyance is that I would like to be able to “save” a Script Component script as a file and then re-use it in other Script Components. Why can’t I do this? I realize I could write a custom component (currently beyond my abilities), but shouldn’t I be able to load code from a .vb file?

Lastly, error messages. Why doesn’t an unhandled error tell me the Script Component’s name instead of the useless, Script_Component_GUID? Just give me something I can easily decipher.

Anyway, I don’t want to sound like I am down on SSIS, Script Components particularly, but I just see areas it could be improved and they seem like reasonable things to ask.

Posted in SQL Server, SSIS | Leave a Comment »

Error Handling in SSIS published on SqlServerCentral.com

Posted by sqlwiseguy on April 21, 2008

I’m excited to report that my first-ever article has been published on SQLServerCentral.com. While I called it Error Handling in SSIS it would probably have been better titled, Logging Data Errors in an SSIS DataFlow Task, as that more accurately identifies the subject. Nonetheless, it is out there, and it was fun to write. Hopefully I’ll get some good feedback from the members at SSC so my writing will improve.

Posted in Published Articles, SQL Server, SqlServerCentral, SSIS | Leave a Comment »