Archive for September, 2011

h1

Convert your old DTS Packages with DTS xChange

September 28, 2011

In my last post I told you about DTS xChange Profiler and how it can help you plan and budget a DTS to SSIS migration.  Now that the planning is done, and hopefully you now have the approval to go ahead with the project, let’s talk about how DTS xChange can now help you actually convert the DTS packages to new SSIS packages.  When you launch DTS xChange from the start menu you will see a menu with 3 options, Profile, Convert and Monitor.  We are going to choose Convert.

image

On the next screen we will tell DTS xChange where our DTS packages reside, either on a file system or on SQL Server. Once we have navigated to the folder containing our packages we next have to specify where the converted SSIS packages will be stored, again this can be either on a file system or on SQL Server. You will also need to tell DTS xChange what the target SSIS platform is, in my case it is SQL Server 2008.

image 

You must also specify if you are planning to run the package on a 64 bit machine or not, but don’t worry if you don’t know there is an option for that too.  There are also some advance options you can configure on this screen as well.

Once you have set the source and destination for your packages its time to select the packages you want to convert.

image

On this screen you can see w have a list of all the DTS packages in the location we specified.  There are several options we can use to select the DTS Packages we want to convert.  My two favorite buttons on this whole screen are the Select All and the Unselect All, simply because of the convenience factor.  It annoys me to no end when there is a multi-select list and no ability to do either.  There is also a search bar at the top which will allow you to find a specific package.  Once you have selected the packages you want to convert they will appear in the pane on the right.  For convenience you can choose to remove packages from that list by highlighting it and click on the remove button, or you can uncheck the package from the list on the left.  Finally you can select Remove All to start the selection process over.  Once your list is set to your satisfaction you can move on to the next screen.

image

This section allows you to institute some best practices and also configure the migration settings. You can navigate through the different sections by either clicking on the tabs across the top or the list on the left. There are thirteen tabs you can configure in this section, but for brevity I’ll just walk you through the first few.  In the first screen you can set the options for creating configuration file including location of the files and whether they are package or connection configurations.  The next tab configures settings for child packages.  The Auditing Framework tab allows you to add an auditing framework into the newly created packages.  All of these tabs allow you to add additional functionality or best practices into you new packages. Once your done configuring the Best Practices and Migration Settings click next.

image

Verify the list of packages that will be converted and click Start Migration.  As the migration progresses you will see the statistics in the Migration Summary pane be updated. Once the packages have finished migrating you will get a Migration Alerts window to pop up which will allow you to review and warnings or errors that occur during migration.

image

By clicking on the Migration Alerts Hyperlink you can see the details of those warnings.

image

The messages you want to pay particular attention to are the Critical Warning and the Error messages. Once they have been reviewed you can choose to save a log of the messages and then click close. That is pretty much it.  Instead of having to manually select the components to replace the existing DTS package components, rewrite SQL statements etc.. or run through the wizard and in next to no time you can convert your DTS packages to SSIS using DTS xChange. 

h1

Plan your DTS Migration with DTS xChange Profiler

September 23, 2011

Converting DTS packages to SSIS can be a tedious and labor intensive undertaking.  If you are like some companies you now have possibly hundreds of these packages to convert and estimating the time and resources it will take to complete the conversion project can be a project in and of itself.  DTS xChange has a very cool feature that will help you accurately project the time and cost to completion of the DTS conversion project.  The DTS xChange Profiler allows you to select the DTS packages on a package by package basis.  So lets say your currently have 300 DTS packages but you will be upgrading only your data warehouse DTS packages, with DTS xChange you can specify only those packages used to load the data warehouse and not the entire 300 packages.  This will allow your to be much more specific with you project plan.  The first step in using the DTS xChange Profiler is to tell it where your packages are located, file system or SQL Server.

image

In this example I have my DTS packages stored in the file system. I navigate to the folder and click next.  On the next screen you will get a list of all the packages that are stored in the specified folder.  There are several ways to select the packages from this screen.  First is the check all and uncheck all buttons, which I think just about every wizard should have if you are allowed to multi select items in a list, it just makes life easier.  you can click one at a time or hold down the SHIFT key to select packages in a range.  You can also search the list for a specific package by using the filter above the list.

image

As you can see the selected packages appear in a list pane on the right hand side which also allows you to remove packages one at a time or all of them from the selected list.  Once all the packages you want to convert are selected click next.  After selecting the packages you wish to profile, you will be prompted tell Profiler how long each type of DTS task takes you or your team to to convert. The times will vary based on skill and experience with SSIS and DTS but Profiler has default estimates. You can also tell DTS xChange what the hourly rate for the SSIS developer is on this project and it will help calculate the cost per package.

image

Another thing you will notice is that the tasks listed in the left pane are color coded to indicate to what extent they can be converted.  Most of the tasks have no problem being converted, but some tasks can’t be converted because they are no longer supported in SSIS. Some tasks like ActiveX Scripts can be partially converted, and DTS xChange will review the objects that you are using inside your task and give you guidance on how to convert those tasks to a similar task inside of SSIS.  Once you are satisfied with your estimates click next and DTS xChange will generate a report for you to the level of detail you specified.   The following is a sample of the report at a detailed level.

image

image

image

image

This report can be exported to PDF or even an excel file.  This would be a huge help in not only creating a project plan but it can also give you the ability to get real numbers in front of decision makers.  All in all a very useful tool, and this is only profiling your packages, wait until you see it convert the DTS packages, but that is for another day.

h1

Its 10 PM, Do You Know Where Your Configurations And Expressions In SSIS Are? BIxPress Does And It Will Tell You Too.

September 14, 2011

With some of the more complex SSIS packages that we develop the number of expressions we use as well as the number of package configurations can become quite large.  That can make remembering which component has an expression and what that expression is rather difficult, and the same goes for configurations too.  BIxPress has a couple of features that can really help you manage this quite easily.

The Expression Highlighter in BIxPress helps you visually identify which components have expressions and which connections have configurations. Here is how it works.  In this package the Data Flow Task currently has no expressions associated with it.

image

Now I’ll quickly add an expression on the description of the data flow task

image

Once that is complete and you hit OK the appearance of the Data Flow task has now changed slightly. There is now a little red circle on the Data Flow Task, which is how BIxPress identifies components with expressions associated with them.

image

The process is pretty much the same for identifying package configurations.  Here are the before and after views of a connection manager with and without the package configuration.

image

image

As you can see the package configuration shows up as a blue circle instead of a red.  Well what happens if you have an expression and a configuration on the same component?  Lets add a expression on the same connection manager we just added configurations to.

image

The connection manager now looks like this.

image

This will make identifying the components so much easier.  What if you don’t want to go through each component one at a time to see which expression each one has.  BIxPress has another feature that lets you see all of the expressions in a list, and not surprisingly its called the Expression List.  Here is how it works.

From the BIxPress menu select the Expression List and a new window will open up.

image

image

As you can see this lists all the components that have expressions.  It gives you each object type, object name, the expression property, and the expression.  You also have the option to edit the expression right from this window using BIxPress’ Expression Editor as well.  With BIxPress knowing where your expressions are and what they are doing is much easier.

h1

Variable Expressions With BIxPress

September 14, 2011

The expression language in SSIS is a powerful but sometimes frustrating tool.  At times it resembles T-SQL at other times some of your typical programming languages which can make implementing them in SSIS packages a daunting task.   There are plenty of samples out on the web spread across blogs and forums, however, BIxPress actually has a library full of some of the most commonly used SSIS expressions already built in. The Expression Manager organizes all your common SSIS expressions for use anywhere you can use SSIS expressions, such as variables and data flow pipelines.  I have found this most useful when trying to build variable expressions.  To launch the Expression Manager when working with variables select Expression Library Icon from the Variable Toolbar Extension

image.

Now that the Expression Manager window is open you should see two tabs across the top.  In the My Expressions tab you add your own expressions to the library which you will then have access to without having to open up another document where you might have stored it, or another package where you might have used the expression before.

image

In the Standard Expressions Tab there are a ton of pre built expressions covering date expressions, string manipulation expressions and file/folder expressions among others.

image

The Expression Manager has saved me a ton of time by not having to search and search for the right syntax.  If you get a chance to try out BIxPress you won’t regret it.

h1

Remove last row from file with SSIS Script Component

September 14, 2011

The other day I had a unique request from a client.  I had been working on a package that would combine a number of smaller files into one larger file and then copy it to a new directory.  An easy enough task using SSIS and some staging tables to insure that the data was sorted in the correct order.  One of the issues that came up was how they were importing the file.  For some reason the last CR/LF at the end of the file, used to start a new line, was showing up as a bad character and causing the process to fail, so naturally they wanted me to remove it.  I didn’t have a clue at fist how I was going to accomplish this, but after asking around the office one of the guys said that it could be done by using a script component.

Well now I at least had a starting off point.  So to Bing and Google I went looking for the most uncomplicated method I could find, since VB and C# are not exactly in my wheel house.  After searching several forums and asking my question in a multitude of ways I finally found what I was looking for. It’s a pretty short piece of script which deletes the last to characters of the end of a file if they match CR/LF.

‘ Microsoft SQL Server Integration Services Script Task ‘ Write scripts using Microsoft Visual Basic ‘ The ScriptMain class is the entry point of the Script Task.

Imports System.IO

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

‘ The execution engine calls this method when the task executes.

‘ To access the object model, use the Dts object. Connections, variables, events,

‘ and logging features are available as static members of the Dts class. ‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

‘ ‘ To open Code and Text Editor Help, press F1.

‘ To open Object Browser, press Ctrl+Alt+J.

Sub Main()

Dim fileStream As FileStream

fileStream = File.Open(“myfile.txt”, FileMode.Open, FileAccess.ReadWrite)

If fileStream.Length > 1

Then fileStream.Seek(-2, SeekOrigin.End)

Dim bytes(1) As Byte

fileStream.Read(bytes, 0, 2)

If bytes(0) = 13 And bytes(1) = 10 Then

fileStream.SetLength(fileStream.Length – 2)

End If

End If

fileStream.Close()

End Sub

End Class

 

I added the script and changed the file name and it worked perfectly.  I hope this helps you guys avoid the time wasted searching for the same thing.

h1

Change Variable Scope with BIxPress

September 13, 2011

One of the biggest annoyances I have when building packages is creating variables in the wrong scope of the package, for instance needing the variable at the package level but mistakenly creating it in the data flow task.  Its not a huge deal to recreate it, but it can be time consuming if you created several variables in the wrong scope.  BIxPress makes it possible to actually change the scope of a variable with a few quick clicks.

image

The highlighted variable is in the wrong scope, it should be in the package scope (LoopLoadArchive).  To change the scope using BIxPress simply click on the button to start the wizard.

image

Here you must choose the variable you want to move, if you want to copy or move the variable, and finally what scope you want it moved to.

image

Click on the green arrow pointing to the right and the variable will be moved.

image

This has been a huge time saver for me on the times where I have had my head down for hours creating SSIS packages only to realized that I now have a dozen variables set to the wrong scope.  This is just one of many of the hidden advantages of developing SSIS packages with BIxPress.

h1

BIxPress Package Builder Templates: Loop Load Archive

September 13, 2011

When I was in the Navy working as a Airplane Mechanic (AM2 if anyone is interested) we were always told never to rely on our memories, but to take the repair manual with us.  Even if it was something we’d done a hundred times or more, the one time you forgot something could put the people who relied on us to fix things correctly in danger.  This mentality has stuck with me even as I’ve changed careers, and that is why BIxPress is such a big help to me even when developing the simple SSIS Packages.  Whether you are using the existing templates in BIxPress or creating your own, it eliminates the guess work and the time spent wracking your brain or scouring the forums and the blogs to find the correct SSIS construction.  In the last few weeks I have had to build a number of packages that loop over files in a folder, load them into a table, and then archive the files.  Not a terribly complex package, but between variables and containers and file system tasks I always tend to screw up one of them and have to spend some time troubleshooting the package to realize I forgot to set the variable as an expression or some other simple thing to overlook. 

BIxPress comes with a template already built in to handle such a process and some test files to load and archive as well.  Inside your project add a new package using the BIxPress Package Builder Wizard, either right clicking on the SSIS package folder or from the BIxPress tab on the menu bar.

From the options select the Create SSIS package from existing package template, also rename the package as well and click next.

image

On the next screen choose the appropriate template.

image

On the next screen choose the configurable properties (the defaults are fine for the test files) and click next

image

On the final screen you see a summary and then click start to begin building the package.

image

Once the wizard is complete there are just a few things left to configure.  The first thing we need to change is the Destination Transform.  I’m loading the files into a table on my local machine.

imageimage

image

Next we need to configure the Flat File connection manager.

image

image

Click OK.  Now lets just verify the test files are where they need to be

image

Execute the package and once its complete the folder where the files were should now be empty of files.

image

and the archive folder should now have three files in it.

image

This is just one of the many package templates that come with BIxPress.  For me it’s a great way to insure that I don’t forget any of the necessary components of a typical package.