Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Tools’ 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 »

Tool Tips

Posted by sqlwiseguy on February 3, 2009

Here a few free programs I have found useful:

  1. MWSnap – a free screen capture tool similar to Snag-it. I use it to produce screen shots fro articles, presentations, and user documentation. It’s not quite as nice as Snag-It, but you can’t beat the price.
  2. PDF995 – this allows you to “print” to a PDF document. I use it to “print” my on-line transactions, especially when I am away from a printer. There is a sponsored version which is free and you can also buy it for the reasonable price of $9.95 if the ads annoy you.
  3. Paint.Net – a free image editing software that was “Originally intended as a free replacement for the Microsoft Paint software that comes with Windows”. Much more powerful than Paint, but simpler and easier to use than Photoshop or Gimp. I found this one when someone else asked about free image editing software on Twitter and they were pointed here.
  4. SQLDigger – an application that searches through SQL Server for text in Stored Procedures, Triggers, and Views. I think I found this one through SQLServerCentral several years ago. I use it pretty regularly, and think it is a good tool, but for some reason development of it stopped with .NET 1.1. I wish it was still being developed.
  5. E-Sword – a Bible program. Includes a few Bible translations and study tools for free with many others available for a minimal fee.

These are a few of the programs and tools that I use on a fairly regular basis and have found helpful in my work and personal life.

Posted in Programs, Tools | Leave a Comment »

RedGate SQL Refactor

Posted by sqlwiseguy on December 5, 2008

I was fortunate enough to receive a copy of RedGate’s SQL Toolbelt, which includes SQL Prompt, SQL Compare, SQL Data Compare, SQL Refactor, SQL Packager, SQL Data Generator, SQL Doc, SQL Backup, SQL Response, SQL Multi Script, and the SQL Comparison SDK. I have used SQL Prompt (intellisense for SQL), SQL Compare, and SQL Data Compare in the past and have always liked RedGate’s products.

Today I had the opportunity to use SQL Refactor to rework several tables in a a database. My co-worker had asked that a table, in a database in development, could be renamed. As you probably know, there is no thing as a simple table rename, especially when it is one of the core tables in the design. In the past I would have to go through every relationship, trigger, view and stored procedure and make the changes, but today, with SQL Refactor, I was able to do it much faster using the Smart Rename function. SQL Refactor plugs into SSMS, so if you right-click on an object you get a Smart Rename option. Once selected you are prompted to provide the new name, and Refactor finds references to the table in foreign keys, triggers, views, and stored procedures, and generates a script for all necessary changes. It does the same for column renames as well. Even with this tool I spent my entire afternoon making changes. Mainly because this one table had references to several other tables, thus every place there was table_id, I needed to make the change to that table as well as Refactor does not rename columns in other tables. Still I would have been looking at probably a full day plus of work without the tool.

The only change I would, and will, suggest to RedGate is to allow you to rename multiple columns in a table at one time so one script is generated instead of having to rename each column separately for the refactoring to be done.

The next tool I will be trying is SQL Response. I’ll probably let you know how it goes.

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

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 »