Category Archives: SSIS

SSIS

SSIS Package Development Troubleshooting Tips

When developing an SSIS package, sometimes it can be frustrating trying to troubleshoot various issues and faults with the package. And if you’re not a seasoned SSIS developer, you may not be sure about the best way to solve your problem. Thankfully, Microsoft has seen fit to bless us with some useful tools for troubleshooting.

Control Flow Troubleshooting

When working in the Control Flow, there are several useful ways to troubleshoot during the development of your SSIS Package:

1) Use breakpoints! Breakpoints can be applied to an individual task or at the package level. A break point will pause the execution of the package at a certain point. You can specify a package to pause at pre-execution, post-execution, on warning, or on error event of a single task, container or package, just to name a few. To enable a breakpoint, right click the task you want to enable your breakpoint on and select Edit Breakpoints. You’ll then be able to specify on which event you would like to pause the package. After adding a breakpoint to a task, you will see a red dot on the task. During execution of the task, you will see the red dot change. To continue execution of a package after it has paused at a breakpoint, simply click the green “Play” button.

SSIS Package Troubleshooting Tips - Edit Breakpoints

2) Check the progress tab! The Progress tab displays valuable information about the execution of your SSIS package. In the Progress tab, SSIS will list all tasks in order of execution and display start and finish times at the task and package level. It even contains message about any errors or warnings that may have occured during execution. If your package failed, this is where the error will be displayed!

3) Use the Debug Windows! The various Debug windows provide you with valuable information during the execution of a package that has breakpoints. Error messages and warning are displayed in the Debug Output window, as well as value of variables during execution. To view the various Debug windows, add a breakpoint at the Pre-Execute event of the package and execute the package. Then in the main menu at the top, click Debug, select Windows, and you will be able to select the various debugging windows. To view the values of variables during run time, open the Watch window and type in the name of the variable you’d like to watch. The watch window will then display the data type and value of any variable you’d like to see.

SSIS Debug Windows

Data Flow Troubleshooting

Troubleshooting the execution of a Data Flow Task is a little different than troubleshooting the Control Flow because you cannot add a breakpoint to a Data Flow Transform. But there are still tools available to us as developers.

1) Use Data Viewers! Data viewers allow us to watch the data as it passes between transforms. A data viewer can display data in a grid, histogram, scatter plot, or column chart format. To add a data viewer to a Data Flow, simply right click a precedence constraint and select Data Viewers. You can then Add and Delete a data viewer.

Data Viewer

2) Use a Conditional Split as a destination! This is kind of a little trick that SSIS developers use that allow you to execute a data flow without writing to a destination. Simply use a Conditional Splitl as the last transform in place of a destination in the data flow and place a data viewer on the precedence constraint directly before the Conditional Split. You can then copy and paste the data from the data viewer into Excel or Note Pad for further review. Or if your place of employment has blessed you by purchasing Task Factory, simply use the Terminator Destination, which requires no configuration and acts as a dead end for your Data Flow during development.

Terminator Destination

3) Use Row Count transforms! Placing a Row Count directly after the source and directly before the destination in a data flow will allow you to closely monitor the number of rows passing through your data flow.

4) And lastly, use the Progress Tab and Debugging Window just like when you are troubleshoot your Control Flow.

Even while using each and every last one of these tips I’ve covered, every once in a while you’re going to come across on error that offers no real explanation or solution. Which brings me to my final and most important tip to remember when trouble shooting your SSIS package: GOOGLE! Laughing

SSIS

Check IsNumeric() with Derived Column Transform in SSIS Package

At some point or another, you’ve probably run into the road block that is the lack of an ISNUMERIC() equivalent within the SSIS expression language. While you can’t use ISNUMERIC() in an SSIS transform, such as a Conditional Split Transform or a Derived Column Transform, that doesn’t mean you can’t check to see if a field is numeric using the SSIS expression language (If you feel so inclined, you can use a Script Task to check if a field is numeric. Tim Murphy, another Pragmatic Works consultant, covers that in his blog here).

As I said before, there is a way we can use a Derived Column Transform (or Conditional Split) to check if a field is numeric. After dragging in a Derived Column Transform into your Data Flow Task, create a new column to be added as a new column to your data flow. Give it a meaningful name and use this expression:

(DT_I4)CheckForNumeric == (DT_I4)CheckForNumeric ? 1 : 0

check for numeric with derived column

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to Ignore failure on Error, as seen here:

Ignore failure

Optionally, you could choose to redirect rows that are not numeric to the Error output of the Derived Column and then handle those rows there.

Now when we run the Data Flow Task, we should see results like this:

IsNumeric results with Derived Column

You can see that the rows that are not numeric have a NULL value for the IsNumeric field we created with the Derived Column. Rows that are numeric have a 1. This way it is easy for us to determine which rows of a certain field are numeric and which are not numeric by checking for NULLs in our field called IsNumeric.

Pragmatic Works, SSIS

Encrypting Sensitive Information in SSIS Package Configurations using BI xPress

One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress, SSIS developers can easily and automatically encrypt any configured property of an SSIS package. So lets walk through using the BI xPress Package Configuration Organizer and look at a couple of the different options we have when encrypting an SSIS package configurations.

There’s a couple ways you can access the BI xPress Package Configuration Organizer. Just right-click on some empty space in your Control Flow design area and select Package Configurations (BI xPress).

Capture1

You can also click the BI xPress menu at the top of BIDS and then select Package Configurations (BI xPress).

Capture1a

This should open the BI xPress SSIS Configuration Wizard. This is the wizard that will allow you to select which package properties you would like to configure and encrypt. Make sure you check the box to Enable package configurations.

Capture4

On the next screen of the wizard, you’ll need to set a few options. The first is to decide what type of Configuration you want to use. You can choose from several options seen below, but I’ll be using an XML configuration file.

Capture4 

After you specify the Configuration Name, click Next.

On the next screen specify which property you want to configure/encrypt. At the top of the window, I can select an encryption mode, I can choose to encrypt only the sensitive data or the full value of the property. In this example, I’m encrypting the connection string for the connection manager OLEDB_SSISOPS_1, so I’ve checked the check box next to the ConnectionString property under OLEDB_SSISOPS_1. We can also select an algorithm type. I’m using the Advanced algorithm to encrypt the connection string. Using these settings, the entire connection string for this connection will be stored in the configuration file in an encrypted format.

 

Capture6

Click Next and on the next screen click Start to generate the configuration file. Once the wizard is finished a summary screen will appear. Click Finish to close the wizard.

And you’re done! Here is a screen shot of the configuration file. You can clearly see that connection string is encrypted and unreadable.

Capture7

Finally, it’s a piece of cake to encrypt sensitive information stored in package configurations!

Pragmatic Works, SSIS

Add 26 High Powered Tasks to your SSIS Toolbox

As a Business Intelligence Consultant with Pragmatic Works, I have the pleasure of being able to take for a spin any of the sports cars in the garage of Pragmatic Works software. And if you’ve frequented my blog before, you’ve heard about how much I love using BI xPress to speed up my SSIS package development. One of the coolest pieces of software out of Pragmatic Works is Task Factory.

Task Factory is a collection of high performance SSIS tasks that allows you to add and extend layers of functionality to your SSIS packages. One of the most powerful transforms that is included with Task Factory is the Dimension Merge SCD, formerly known as the Kimball SCD transform.

Dim Merge SCD

The Dimension Merge SCD transform is 100 X faster at handling Type 1 and Type 2 slowly changing dimensions than the native Microsoft SCD component which uses the slow-as-mess OLE DB Command transform. Being able to handle very large dimensions up to several million rows makes the Dimension Merge SCD included with Task Factory one of the most powerful transforms included with Task Factory. The Dimension Merge SCD offers the power and flexibility of the Kimball SCD with the support of the expert staff at Pragmatic Works.

One of my personal favorite new transformations just recently added to Task Factory is the Regex Replace transform.

Regex Replace

Previous to the development of the Regex Replace transform, I would have to use a Script Component and write some code to use regular expressions. But with the Regex Replace transform, I can easily and quickly use reg ex to either replace reg ex matched data or extract the reg ex matched data to a new column or the existing column.

The newest version of Task Factory includes 26 SSIS tasks and transforms that definitely are worth taking a look at. And if you’re even a little bit familiar with Pragmatic Works, you know of our dedication to the SQL community. With that said, Pragmatic Works offers a completely free Community Edition version of Task Factory, which includes five tasks and transforms such as the File Properties Task and the Data Validation Transform. Go here to download the latest version of Task Factory.

SSIS

Execute Child Package in 32 bit Mode From 64 bit Master Package

If you’ve ever built an SSIS package that utilizes the Microsoft Jet Driver for an Access or Excel data source or a package that executes a SQL 2000 DTS package, you know that you must set the Run64bitRunTime property in the SSIS project properties to False. Continue reading Execute Child Package in 32 bit Mode From 64 bit Master Package →

SSIS

Using BCP Utility in SSIS

The Bulk Copy Program Utility (BCP) is a very nifty little command line tool that allows us to bulk copy SQL Server data from or to a data file in a user specified format. The BCP is very useful when you need to copy large amounts of data into a data file for use in other programs or if you need to back up your data in a specified format for a third party. But the BCP is also very useful when you need to copy large amounts of data from one SQL Server table to another table. There are certain cases where using the BCP in SSIS may be the better choice over a Data Flow Task if no transformations are needed.

To use the BCP from SSIS we’re going to need 2 Execute Process Tasks (EPT) in our SSIS package: One Execute Process Task to pull the data from our table into a file and another one to pull the data from the file into our second table. And for the purposes of this little post, I’ve put it in a Sequence Container.

Capture

Then in our first EPT, we need to set up a couple different properties. The first is the Executable property. This tells our EPT which process to execute. In this case, it’s the Bulk Copy Program executable, bcp.exe, duh Winking smile. If you’re not running SQL Server 2008, obviously the BCP isn’t going to be located under the 100 folder.

Capture2

Secondly, we’ll need to specify our arguments. Now there are many different switches you can use, such as –e to generate an error file, –T to use integrated security when connecting to SQL Server, or –b to specify the rows per batch, but you can check out this article on MSDN to learn about all the different switches available. I would highly recommend reading through the article to learn about the different switches available. For our example, we’re going to use a few different switches to specify things like an error file (-e), using the native data types of the data (-N), and the server (-S).

Our first argument is the fully qualified database.schema.table which we would like to pull data from. In this case, it’s the table PaidVins2 in my CashForClunkersDW database. And since we are extracting data from a table, we will use out for the second argument of the BCP command. The third argument of the command will be the location of the BCP file.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp”

So far our BCP command should look like what you see above. But we still need to add a few switches before this will work. We at least need to specify the format of the BCP file using –N, which will create the BCP file with the data types native to our table. We could use –x to specify an xml formatted file or –c to specify character data types for all fields.

We also need to use the –T switch to let BCP know it’s a trusted connection. In place of –T we could use –U and –P to specify a username and password.

Next we’ll use the –S to specify the server and –e to create an output file for any errors that could occur.

CashForClunkersDW.dbo.PaidVins2 out “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins2errors.txt”

Enter your BCP command in the Arguments property of the Execute Process Task. If you execute the task, you should see the .bcp file created in the directory you specified. If you used the character (-c) format and specified the file as .txt (and the file is not too big!), you should be able to open it in a text editor.

The last thing we need to do is add an additional Execute Process Task to copy the .bcp file we just created into our destination table and connect the two with a precedence constraint. In my case, the table is PaidVins3. But instead of using the out switch, we’ll us in.

CashForClunkersDW.dbo.PaidVins3 in “C:\Users\dryan\paidvins2.bcp” -N -T -S”FL-WS-CON-DR01″ -e”C:\Users\dryan\paidvins3errors.txt”

And we’re done!

Capture3

Now you can BCP large amount of data quickly from SSIS.

Free Training, Pragmatic Works, SSIS, SSRS

Help Me Help You With Free SSIS/SSRS Training

As you may have noticed, Pragmatic Works is once again running another promotion. This promotion is a little different though in that it’s probably the best deal we’ve ever had on our software/training bundle.

If your company purchases at least a single, lonely license to any of our incredible software, inlcluding Task Factory, BI Documenter, BI xPress, and/or DTS xChange, and you get a free seat in either our SSIS or SSRS class. The really great thing about these classes is that you can attend the class remotely via GoToMeeting. The classes are about 4 hours a day for 4 days which allows you to perform your daily duties and still attend the training class.

My personal favorite bundle is the BI xPress/SSIS Training Class bundle. If you’re interesting in learning SSIS, this would be the perfect combo to get you ramped up well past beginner level and headed towards an advanced level. Our SSIS Training Class starts out at a beginner level and progresses all the way through some advanced SSIS concepts. This plays right along with the strengths of BI xPress. Once you take our SSIS Training Class and learn to use BI xPress, you’ll be developing and implimenting SSIS packages at an incredible pace.

I’d highly suggest any of you at least consider this deal before it expires at the end of this month! Help me help you! To get more information, contact Sales@PragmaticWorks.com. You won’t regret it!