Tag Archives: BI xPress

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.