Tag Archives: SSIS

Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

image With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.

Configure the Excel Power Refresh Task in SSIS

Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.

First create a Connection Continue reading Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

My Top Four Books for the MS Business Intelligence Professional

As a Business Intelligence Consultant, I do a decent amount of speaking, interacting with the community, and have written and contributed on a few SQL Server books. A question I’m often asked is if I can recommend any good books which brings me to this blog post. I wanted to make you aware of four books for learning data warehousing and  other MS BI technologies that I’ve found incredibly helpful over the years I’ve spent designing and implementing enterprise data warehouse and business intelligence solutions. Continue reading My Top Four Books for the MS Business Intelligence Professional

Adding/Copying SSIS XML Package Configurations

SSIS Package Configurations allow us to modify SSIS package properties from outside of the packages. This gives us a seamless way to dynamically point our packages to our development, QA, and production environments without needing to manually open and modify our connection managers within each SSIS package. Package Configurations can be created using a SQL Server table, environment variable, XML files, registry entry, or parent SSIS package, but this post will focus on creating an package configuration using an XML file.

To create the Package Configuration, open an SSIS package in SSDT or BIDS and right-click in the blank area of the control flow and select Package Configurations.

image

Check the checkbox to enable package configurations and then click Add to add a package configuration to the package.

image

In the drop down list next to Configuration type select XML configuration file. Browse to the location where you wish to store the XML configuration file and type in the name which you’d like to give your configuration file and hit enter.

image

And now time for a quick note on best practices and my thoughts on using XML configuration files. SSIS package configurations are most often used to configure Connection Managers from outside the SSIS packages. With this in mind, I prefer to create one package configuration per Connection Manager. I also prefer to name the configuration file the same name as the Connection Manager. In my experience, the 1:1 ratio of XML configuration files to Connection Managers eases maintenance and management of the XML configuration files. If you’re creating XML configuration files to configure other properties of the package such as package variables or task properties, you may find another solution works better for you. That’s just my .02 cents on the matter.

Now we need to specify the property which we wish to configure. In this example, I’m selecting the ConnectionString property of a Connection Manager. Check the checkbox next to the ConnectionString property under the Connection Manager. Click Next. On the final screen you’ll need to give the Package Configuration a name. I give the same name as the XML Configuration File to help things stay nice and neat.

image

We can see our package configuration has been created. Click close.

image

Yay! Now your package has an XML configuration file set up to dynamically configure your connection string of the connection manager you selected at run time. All that is left to do is to copy the XML configuration file to each environment the package will be run on: developer machines, development, QA, and production environments. Then you must also update the XML configuration file with the environment’s connection string. Just open the XML file in note pad and update the Connection String to point to the appropriate server and database.

image

So we’ve set up the package configuration on a package. But what if we need to copy the package configuration to many other packages? Sure, we could complete the previous steps manually on each package, but that would take a lot of time. We can, however, easily accomplish this in a few clicks with BI xPress. In order to complete the following steps you will need to have BI xPress installed. You can download and install a free trial version of BI xPress here.

image

Shift select all of the packages within your SSIS project including the package that has the package configuration applied as well as the packages you wish to modify. Then right-click the packages in the Solution Explorer and select Package Configurations (BI xPress) to open the BI xPress Configuration Wizard.

image

Click Next pass the first screen of the Configuration Wizard.

image

You should see all of the packages highlighted and in the Select Items window. Click Next.

image

In the following screen you should see your packages listed and the lone package configuration displayed beneath the SSIS package we added the configuration to.

image

Right-click the package configuration and select copy or simply highlight the package configuration and hit Ctrl+c to copy the package configuration.

image

Then select each package in the list individually and hit Ctrl+p to add the package configuration to each package.

image

Click the disk icon to save your changes and we’re done! Our package configuration has been added to each package with much less work than we initially went though.

image

Just be aware that if package configurations have not been enabled for the packages you added the package configuration to you will still need to complete that step for each package.

For more information on BI xPress just head over to PragmaticWorks.com or head here to download the free trial version of BI xPress. Enjoy!

Are You Using SSIS Best Practices?

image

Earlier this month, Pragmatic Works released a new tool for their Pragmatic Works Workbench toolbox: The Best Practice Analyzer. The guys and gals over at Pragmatic Works have come up with a tool that analyzes your existing SSIS packages and compares them with a standardized set of best practice guidelines produced by the expert team of consultants and developers you’ve come to know and love over the past several years.

The Best Practice Analyzer combs through the SSIS packages you have selected and produces a report outlining all the best practice violations including violations of varying severities, such as “Warning”, “Error”, “Performance”, and “Informational”.

First, select the packages you’d like to investigate for adherence to best practices.

Next, select the best practices to be included in the report.

The end result is an easy-to-read report identifying any violations of best practices.

Violations could include everything between leaving a component description blank, using a fully block transform in a Data Flow Task, not enabling error logging, plus many more. Head over to PragmaticWorks.com to get more information on the SSIS Best Practice Analyzer.

Migrating Away From Informatica To SSIS? You’ll Need Workflow Profiler

image

If you’ve been paying attention to this blog or PragmaticWorks.com, you probably picked up on the fact that Pragmatic Works has put together a new service offering for those companies currently running Informatica as their ETL tool of choice and are sick and tired of paying hundreds of thousands of dollars a year in licensing (and in some cases millions of dollars! o_O!!). Pragmatic Works is now offering to quickly and efficiently migrate their clients from Informatica to SQL Server. One of the ways Pragmatic Works makes this process less painful is by using their tool, Workflow Profiler.

The primary goal of Workflow Profiler is to fully document your Informatica PowerCenter MetaData environment very quickly, thus giving you a better idea of just how much effort could be required during the migration process. This information is displayed in a series of detailed reports viewable in Workflow Profiler.

Reports, such as the Widget Distribution Summary Report, give you a detailed analysis of your Widget distribution.

Reports like the Workflow Detail Report, Mapping Summary Report, Mapping Detail Report, and many others display all types of information regarding your Workflows, including widget usage, what tasks are used in the workflows, which workflows use parameter files and more.

There is also the Workflow Execution Summary Report. This report displays all kind of performance data and metrics including execution status, performance numbers for each workflow and even individual tasks, and success/failed row counts for sources and targets for each session task.

If you’re looking for more insight into your Informatica environment and would like to know more about what kind of performance you’re getting out of your Workflows, I recommend checking out Workflow Profiler. Download the free trial here.

Generating Junk/Test Data For SSIS

Everyone once in a while I’ll run across a requirement for me to generate a bunch of junk data for the sole purpose of performance testing or benchmarking. There are plenty of ways that I could generate my test data, including clever TSQL scripts or a Data Flow Script Component as a source. And those methods work well, but are somewhat limited if I need to generate unicode string data for testing. But that’s where Task Factory comes in.

imageIncluded in the latest release of Task Factory is the Placeholder Source. The beauty of the Place Holder Source is that I can quickly and easily generate test data for my package.

Here you can see the editor for the Placeholder Source. On the Component Properties tab, I can specify how many rows of junk data I’d like to generate. The default is 10,000 rows, but I can set it to whatever I like. I’ve tested the source component with 100 million records before with no problem.

image

On the Input and Output Properties tab is where you specify which columns and data types you’d like to include in your junk data.

image

By default, the Placeholder Source creates 5 output columns of various data type, but you can add more fields of whatever data type you like. Just click Add Column and then select the data type you want. Here I’ve added an additional column and selected Integer as the data type.

All that’s left to do is wire up my package and click execute. Instantly tons of junk data is created for us to test or play with. If you’re interested in expanding your SSIS arsenal, head over to PragmaticWorks.com and download Task Factory. The best part is that Task Factory is free to download. A license is only required to run the Task Factory components remotely. So why not check it out?

SSIS Advanced Execute Package Task with Task Factory

In the last few months, I’ve been giving a preview on some of the new advanced SSIS tasks and transforms being released in the coming version of Task Factory. Well Task Factory 3.0 has finally been released last month, and its packed with a ton of really cool tasks and transforms that are going to make SSIS development easier and will increase the performance of your packages.

image

One of the newest SSIS tasks released in Task Factory 3.0 is the Advanced Execute Package task. The really cool part about this task is the MUCH improved interface. It’s now incredibly simple to set up variable mappings from a parent package to a child package.

Here you can see the editor for the Advanced Execute Package Task. There are three areas of the editor I want to take a look at.

image

The first area, highlighted in green, is where you specify your connection to the child package. If the package is in the file system, you simply browse to the location of the package. If the package is located on a SQL Server, you need to select the SSIS connection manager to use. In either case, if the package has a password, you’ll need to enter that.

The second area of the Advanced Execute Package Task, highlighted in yellow, is where you create mappings between variables in the parent package and variables in the child package. In the Assignment Direction drop down list, you have a few different options.

image

We can read a variable from the child package, write a variable to the child package, or do both simultaneously. In my case, I’m passing a variable to the child package so I will select “Write variable to child package”. Then I just need to select the Child Package Variable and the Parent Package Variable.

image

Once I’ve done that, I can just click “Add Mapping”. Now we can see the mapping created between the two package variables in the last area (highlighted in pink in figure 1). Using this method, we can create multiple variable mappings between the parent package and the child package.

image

As you can see the Advanced Execute Package Tasks from Task Factory is very intuitive and simple. If you’ve used the native SSIS Execute Package Task, you’ll recognize that the new Advanced Execute Package Tasks is much simpler and easier to configure.