Category Archives: Pragmatic Works

Pragmatic Works, SSIS

Manage, Save, and Share SSIS Expressions

If you’re even slightly familiar with SSIS you know that one of the incredible strengths of SSIS is the ability to make packages dynamic in countless different ways. One of the ways SSIS accomplishes this is through the use of SSIS expressions and the expression language.

BI xPress has a really useful feature called Expression Manager. The Expression Manager is a very cool feature that allows an SSIS developer to create, share, and manage custom SSIS expressions.

Creating your own SSIS expressions is very easy. Simply open BI xPress and click the Expression Manager button in the top right of the window.

Capture 2

Or if you’re already developing a package in BIDS, go to your variables window and click the Expression List icon.

Capture 3

Now that the Expression Manager is open, we can browse the canned SSIS expression or create our own. Let’s create our own custom SSIS expression for us to use later on or share with our development team.

First, click the Create New Expression icon.

Capture 4

Then give your expression a name and a description. My expression is going to append the user’s name and the date to create a file name. Here you can see the expression.

Capture 5

And in case you want to copy it for use:

"C:\\DataOut\\Archive\\"  + RIGHT(@[System::UserName],FINDSTRING(REVERSE(@[System::UserName]), "\\", 1)-1)
+ (DT_WSTR,4)YEAR(GETDATE()) + "-"
   + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "-"
   + RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".txt"

In my expression you can see that I leverage the UserName system variable to get the name of the user executing the package. I use FindString and Reverse to eliminate the domain and \ from the UserName, as well. Once you’re done, click Save and then click OK to close the Expression Manager.

Now that our expression is saved, we can use our expression in our SSIS packages. To utilize our expression in the package all we need to do is the use the BI xPress Expression Editor. We can clearly see our new expression in the My Expressions folder.

Capture 6

Besides allowing a user to manage their own custom SSIS expression, the Expression Manager features a host of canned, commonly used SSIS expressions. This drastically reduces the learning curve of the expression language. The Expression Manager comes with expressions that can create a file path and name with the date appended, create a dynamic SQL statement, or calculate the beginning of the previous month, just to name a few.

Capture 1

As you can see, the Expression Manager is a very powerful tool that allows you to fully realize the power of the SSIS expression language. To download a free trial version of BI xPress, check out PragmaticWorks.com.

Pragmatic Works, SSIS

Quickly Apply SSIS Package Configurations to Multiple Packages with BI xPress

One of the most powerful features of BI xPress, in my opinion, is the Package Builder Wizard. The Package Builder Wizard allows an SSIS developer to create a template from a package or a from a few components of a package. The developer can then use those templates to create new packages or alter existing packages. In this short post, I’m going to walk through creating a template that contains a package configuration and then show you how to apply that template to all the packages in my SSIS project.

Recently I was consulting for a client that required me to add multiple package configurations to dozens of existing packages. Using the follow method, I completed that work in less than an hour. Needless to say, the client was very impressed and has since bought many copies of BI xPress.

If you’d like to follow along with me, visit http://www.PragmaticWorks.com and download the free trial of BI xPress.

I’ve got an SSIS projects that has a few packages in.

Capture1

My requirements are that I need to put package configurations on each of these packages so that way I can manage the package’s connections outside of BIDS.

The first thing I need to do is create package configurations for the properties I’d like to configure from outside the package. In my case, I’m creating two package configurations: One for my connection to my destination database and another for the connection to my auditing database.

Capture2

To create a package configuration, right-click in a blank area of the Control Flow and select Package Configurations.

Capture3

With the Package Configuration Organizer, make sure to check the check box to Enable Package Configurations. Then click the Add button. Next select the type of Configuration. In this example, I’m creating an XML configuration file (.dtsConfig). Specify the file path and name of the dtsConfig file. Then click Next.

Capture4

On the next screen, select the property that you want to configure. I’m going to select the ConnectionString property for the CashForClunkersDW Connection Manager. Click Next.

Capture5

On the final screen, give the Configuration a name and click Finish. I then created an additional configuration file for my other connection. Here you can see the two configurations I created.

Capture6

Now that I’ve created the package configurations I want to apply to all my packages. I need to create a template. To create a new template, right-click in an empty area of the Control Flow and select Package Builder Wizard (BI xPress).

Capture7

When the wizard opens, select the option on the top left to create a New Template (From Selection). Specify the Template Name and select the folder to store the template. Click Next.

Capture8

On the next screen, select the package objects to include in the template. Scroll all the way to the bottom of the Configurable Objects window and select the Package Configurations. Then click the Next button.

Capture9

Then click Start on the next screen to create the template. Now that the template is created, now we can apply our template to the rest of our packages. In the Solution Explorer, select the packages you want to apply the new template to by holding Ctrl and clicking. Then right-click one of the selected packages and select Package Builder Wizard (BI xPress).

Capture10

Make sure the option to Modify Packages (From Template) is selected. Click Next.

Capture11

Then I’m going to select the template I just created.

Capture12

On the next screen you should see the packages you selected in the Solution Explorer. You can select other packages or deselect. Click Next again. On the next screen, click Next again.

If a warning message appears letting you know that the package layouts will be modified, click Yes to continue. Then click Start to apply the template to the packages.

After applying the template to the other packages, make sure you open each package and check the configurations just to make sure everything went as planned before you start testing. And that should be it! You just added package configurations to all of your packages in a matter of minutes using BI xPress.

Pragmatic Works, SSIS

Audit and Watch SSIS Packages Execute on the Server

1 Comment

Among the many cool features of BI xPress is the extensive and robust Auditing Framework available right out of the box. With the BI xPress Auditing Framework, a user can track errors, warnings, row counts, variable, connections, source queries, and many other package properties. Whether you’re applying this incredible Auditing Framework to a single package, a dozen packages, or 100 packages, stepping through the wizard only takes a few moments. In this post, I’m going to walk you through applying the Auditing Framework to an SSIS package.

We’ve got a simple package that loads a dimension in a small data warehouse based on the Cash For Clunkers data.

Capture1

Now lets add the BI xPress Auditing Framework. To apply the Auditing Framework, I can simple right-click the package in the Solution Explorer and select Add/Remove Auditing Framework (BI xPress).

Capture2

This will launch the Auditing Framework Wizard. After clicking Next on the opening screen of the wizard, select the option to apply the Auditing Framework. Likewise, you can remove the Auditing Framework by selecting the other option. Click Next.

Capture3

Select which packages you’d like to apply the Auditing Framework to. You can select packages in a File System, on a SQL Server, or in an SSIS project to name a few locations.

Capture4

Click Next. Now you’ll have to create the Auditing database or select the Auditing database if it already exists. If you need to create the database, specify the server you want to create the database on and click the Create New Database button.  Make sure you click the Create new connection button to create the Connection Manager in the SSIS Package that points to the Auditing database. You can also check the check box to create an XML Package Configuration file.

Capture5

Then click the Logging Options tab. On this tab, you can specify DataFlow Logging, Variable, Connection, and other logging properties. Using these options, we can specify to log dataflow source and destinations row counts, real time data flow monitoring, connections, variables, and other properties. All of these options are selected by default so we’ll leave these selected.

Capture6

On the Advanced tab, you can specify if there are any other customer user variables that you would like to log. And on the Alert Filtering tab, you can specify which warnings you do not want to log, for example any truncation warnings using the Warning Editor.

Capture7

Click Next. Then click Start to apply the Auditing Framework to the package. When the process is complete, click Finish. Now that we’ve successfully added the Auditing Framework to the package, lets take a look at it.

The first thing you’ll probably notice is the new Connection Manager connecting to our Auditing database where all our auditing data is kept. Then if you open any data flow tasks, you’ll notice that they appear “jumbled up” after adding the Row Count Transforms. Simply reformat the data flow. Notice the Row Count counting rows out of the source and the second Row Count transform counting rows into the destination. The row count info is stored in variable.

Capture8

This information is then written to our database using tasks located in our SSIS package’s Event Handlers, such as OnError, OnWarning, OnPreExecute, OnPostExecute, and possibly in the OnVariableValueChanged Event Handler if you specified to track variable change history.

Now that we’ve applied the Auditing Framework, I’ve deployed the package to my server and executed it a few times in order to create a history of executions. One of the really cool things about BI xPress is that after applying the Auditing Frameworks we can watch our SSIS packages execute on the server as if we were watching them in BIDS! To open the BI xPress Monitoring Console, click the BI xPress menu in BIDS and select SSIS Monitoring Console.

Capture10

In the SSIS Monitoring Console, one of many reports you can use to troubleshoot your SSIS package executions is the Execution Control Flow Diagram. We can watch the Control Flow and Data Flow while the package executes on the server. Any Errors and Warnings that occur will be instantly visible and we can also see the values of logged package variables. To see a full list of all the reports available right out of the box, click this link.

The BI xPress Auditing Framework is unlike any other out of the box framework available. It’s complete and thorough and gives us, as SSIS developers, more than Microsoft ever dreamed in regards to SSIS troubleshooting. To download a free trial of BI xPress, visit PragmaticWorks.com.

Free Training, Pragmatic Works, SSAS, SSRS

Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Training, Pragmatic Works, SSAS, SSRS

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

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

The Best SQL Documentation Tool on the Market

If you read my blog you know from time to time I will blog about an exceptional third party SQL tool. Previously I’ve blogged about Task Factory and BI xPress, two amazing SSIS development tools. Well this week I figured I would talk about a killer SQL documentation tool called BI Documenter.

image

BI Documenter allows you to quickly and easily document your entire BI environment. So unlike the other popular tools out there, BI Documenter will document SQL Server databases (2000, 2005, 2008 & R2), SSIS packages, SSAS cubes, and SSRS reports. Because all of these objects can be documented in a single snap shot, you are then able to easily perform impact analysis across all objects, which has a pretty slick UI and is very intuitive. You can also do snapshot comparison across your whole stack, so you can see how any object in a snapshot differs from a previous snapshot.

image

One other thing to mention about documenting SSIS packages is that BI Documenter is the only documentation tool that not only documents the SSIS packages textually, but also visually!

screen30

This means that after you document you SSIS packages, you can view the SSIS packages (Including the Data Flow Tasks!) just like you would in BIDS. Very cool!

BI Documenter is a pretty cool documentation tool that is very well rounded and since it’s the only tool on the market that can document the entire BI stack all together, its basically you’re only choice if you want to do impact analysis across the stack. BI Documenter also has a lot of awesome features coming up in the next release such as documenting server level objects, like SQL Agent Jobs, Linked Servers, Backup Locations, and others.

If you’re in the market for a rock solid documentation tool with the kind of support you can count on, I’d highly recommend that you check out BI Documenter and download the free trial. It’s at the very least worth a look.

image

Pragmatic Works, SSAS

SSAS Workshop in Phoenix, AZ 6/21 – 6/22

Capture

Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

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.

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!