Category Archives: SSIS

Pragmatic Works, SSIS

Migrating From Informatica To SSIS?

image

If you’re even considering migrating your Informatica Workflows to SSIS packages, you’re going to need a clear understanding of the level of difficulty you’re going to be up against. That’s where Workflow Profiler comes in.

The Workflow Profiler allows you to gather a complete list of of repository objects at the most granular level with the goal of determining their reusability. The Workflow Profilers also identifies connections utilized within the Workflows. Once the workflows have been documented, detailed reports are generated detailing the objects contained in the Informatica workflows.

First, identify the repository connection.

Then select the Workflows you want to document.

Next, you can select the type of report you’d like to generate. You can also specify if you would like to filter out certain types of objects.

And you’re report is ready for viewing and exporting to Excel, Word, or PDF formats.

Head over to PragmaticWorks.com and download the free trial of Workflow Profiler. If you’re considering migrating your ETL to SSIS, this is a tool you don’t want to miss out on.

Community, Free Training, SQL Server 2012, SSIS

Notes From SQL Saturday 168: Introduction To SSIS 2012

Thank you to everyone who attended my session, “SSIS 2012 In Yo’ Face: Introduction to SSIS”! I had a great time presenting and it was a whole lot of fun. Also, a big thank you to my co-presenter, Mitchell Pearson (blog | twitter)!

Also, thanks to everyone who attended my second bonus session, “What’s New In SSIS 2012”.

For those of you interested, here is the package we created in “SSIS 2012 In Yo’ Face”. Simple place the content of the zip file in the C:\Projects\SQLSaturday168 folder and unzip the SSIS project to the directory of your choice. If you have any questions, please feel free to post them here or send me a message on Twitter (@SQLDusty).

Again, thanks to everyone who came to my sessions! I hope you enjoyed the sessions and maybe even learned a little something.

Free Training, SQL Server 2012, SSIS

I’m Speaking at SQL Saturday #168: BI Edition!

imageSQL Saturday #168: BI Edition is coming up on November 17th in Tampa, Florida and yours truly will be presenting! This SQL Saturday event will be all Business Intelligence focused! All the sessions will either be ETL, analytics, or reporting related with a miscellaneous track focusing on professional development and DBA topics. Get registered ASAP for this awesome BI focused SQL Saturday!

I’ll be co-presenting with Mitchell Pearson (blog | twitter) on an introduction to SSIS 2012 called SSIS 2012 In Yo’ Face: Introduction to SSIS. We’re going to cover commonly used SSIS tasks and transforms, the SSIS expression language, and how to create powerfully dynamic packages.

So head over to the SQL Saturday #168 site and get registered. I look forward to seeing everyone in our session! It’s going to be a blast!

Pragmatic Works, SSIS

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?

Pragmatic Works, SSIS

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.

Pragmatic Works, SSIS

Sneak Peek: Advanced Conditional Split Transform in Task Factory 3.0

IMG_8325

You may have seen my previous blog post a couple weeks back highlighting some of the neat things the dev team of Pragmatic Works is including in the latest build of Task Factory. Well I was doing some more sneaking around and I was able to snag these screen shots of the new and still experimental Advanced Conditional Split Transform!

I think one of the biggest and coolest improvements is the new functions made available for us in Advanced Conditional Split Transform. IMG_8328You’ll notice the editor for this transform is very similar to the Advanced Derived Column Transform we looked at last time. Now you have access to a ton more functions and expression that allow you to do some very cool things, like data cleansing with Regex, encrypting/decrypting fields, and access parts of file names with an expression. The SSIS expression language has been vastly improved and expanded in these transforms and is now more intuitive and easier to use than ever before.

IMG_8327One of the other things that is new with the Advanced Conditional Split transform included in Task Factory 3.0 is the ability to test and validate expressions within the editor for the Advanced Conditional Split transform. Not only can you validate your expressions in the editor before run time, you can actually test the performance of the expression you have written by specifying a number of iterations to test the expression with. This should give you a good idea of how the expression will perform during run time. This is a feature we haven’t had before. Now those of us who are SSIS developers will be able to test, fine tune, and retest our SSIS expressions!

This new generation of SSIS tasks and transforms are going to change the way I build my SSIS packages due to the new flexibility and power. Stay tuned for future posts on some of the incredible improvements and additions that are going to be available in the next version of Task Factory by Pragmatic Works.

Pragmatic Works, SSIS

Sneak Peek: Super Advanced Derived Column Transform In Next Release of Task Factory

2 Comments

Don’t ask how I was able to acquire these screenshots or what favors I may or may not have performed in order to snag these pics of the next release of Task Factory. I’m not proud of what I’ve become. But the fact of the matter is that Task Factory 3.0 is going to be insane!

Task Factory 3.0 has about 10 or so new tasks and transforms that radically expand the power of SSIS. One of the new Data Flow transforms included in Task Factory 3.0 is the Advanced Derived Column Transform. The Advanced Derived Column Transform includes a ridiculous amount of new expressions and functions. Sources tell me to expect around 180 or so total functions in the Advanced Derived Column Transform.

I snagged these screenshots which show some of the new functions. You can see what appears to be a host of Regular Expression functions available for use in the transform! Yes! It’s about time!

IMG_8082

Also, it appears there a bunch of pretty interesting functions having to do with parsing file paths and names, such as GetDirectoryName, GetFileName, and GetFullPath, among others.

IMG_8083

One of the coolest features of the Advanced Derived Column Transform that I’m really excited about is the Expression Validation pane. Not only can you validate your expression and preview the expression results, you can also test your expression for performance!

IMG_8084

This new version of Task Factory is going to be sick. Keep on eye on PragmaticWorks.com for news of the release, which I’m told should be sometime in the next month. I’ll keep you posted so keep checking back here for more information.

SQL Server 2012, SSIS

Upgrade 2005/2008 SSIS Packages to 2012 Like a Boss!

12 Comments

With the release of SQL Server 2012 comes a whole host of improvements to Integration Services that makes development and administration of your SSIS packages much easier. And it also looks new and shiny, so you can’t beat that.

After you’ve upgraded to SQL Server 2012, you’ll have to start converting your SSIS packages to 2012, which isn’t all that difficult. There are, however, some fundamental changes to the way packages are built, configured, and managed that are important to know. In this article, we’re going to walk through upgrading an SSIS 2008 project to 2012.

First, lets open SQL Server Data Tools.

0 open SQL Server Data Tools

Then we’ll need to open our existing SQL Server 2008 Integration Services project.

1 open project

Once the navigate to the project and open it, the Visual Studio Conversion Wizard will open. This will walk you through converting the 2008 IS Project to 2012.

2 VS conversion wizard

After clicking Next, you’ll be asked if you want to create a Backup. I opted to create the Backup in case something went horribly wrong. Hopefully you’re packages are in source control so you have something to fall back to, but I didn’t convert the original packages anyways. I created a copy of the project and then converted the copy in order to preserve the original project.

3 backup packages

Click Next a couple times and surprisingly, or not, another wizard will open. The SSIS Package Upgrade Wizard is where we’ll select which packages we want to upgrade to 2012.

4 ssis package upgrade wizard

Place a check mark next to the packages you wish to upgrade. In my case, I only have one package in this project, but if I have multiple packages I would see those packages displayed in the window below.

Also, this is where you will specify a password if the packages are encrypted with one.

5 select packages give pw

On the next screen of the wizard, there are a couple of important things to point out here.

6 select ssis package mngmt options

The first option, “Update connection strings to use new provider names”, allows the upgrade wizard to update any connection strings to use the SQL Server 2012 Native Client Library. This will upgrade any connections stored in your package. What this won’t upgrade are package configurations or any expressions that may be altering a connection manager. Those will need to updated manually.

Also, the last option, “Ignore configurations”, basically allows you to tell the wizard to not warn you about potential problems with package configurations on connection managers. I left this option unchecked because I want to know which packages have configurations on the connections that could potentially cause problems.

In the Upgrade Report, you’ll notice the first informational message indicates that the provider was upgraded to SQLNCLI11. Also, the upgrade wizard detected a package configuration attempting to configure the connection. We either need to upgrade the package configuration to use the new provider name or remove the configuration.

7 view upgrade report

If I open the package in Data Tools, you’ll see the following error due to the lack of support for the old provider.

8 package config provider error

So at this point we have two options. The first option is to update the package configuration and continue using our package configuration with our 2012 packages. Or we can take advantage of a brand new feature in SSIS 2012: Parameters! With your package open in Data Tools, you’ll notice the Parameters tab. This is where you’ll manage your parameters that are scoped to the package. Parameters that are scoped to the project will appear in a seperate .params file that can be seen in the Solution Explorer. Parameters are designed to replace package configurations and they function like you’d imagine a parameter would. Parameters can be assigned values and the parameters can be shared between packages in the project.

9 create parameter for package config

Before we can start using parameters in our packages, we must first convert our entire SSIS project to use the Project Deployment Model. The Project Deployment Model is also new to SSIS. Basically, with the Project Deployment Model, an SSIS project is the unit of deployment for the project, whereas with the legacy deployment model, Package Deployment Model, a package was the unit of deployment. The Project Deployment Model is how we will be able to share parameters between packages. Read this article to learn more about the Project Deployment Model. If your SSIS project is currently set to the legacy Package Deployment Model, the project icon should look like this:

16 package deployment model icon

Once you’ve converted to the Project Deployment Model, the icon should look like this:

17 project deployment model icon

To upgrade your convert to the Project Deployment Model, right-click the project in the Solution Explorer and click Convert to Project Deployment Model.

10 convert project to project deployment model

This will open, you guess it, another wizard. You’ll first select the packages to include in the project. You’ll also need to specify a few project properties, like the name of the project and the encryption level. If you are upgrading any packages with Execute Package Tasks in them, you should upgrade those to use a project based reference.

The important part of the Project Conversion Wizard is converting the package configurations to Project Parameters. On the Select Configurations screen, check the package configurations that you wish to convert to parameters.

11 convert package configs to parameters

Next, you’ll need to create the parameter that will replace the configuration. Any configurations properties in the configuration file(s) will be shown here. Specify the Parameter Name and the Scope.

The Scope can either be at the Project or Package level. If you create the Parameter at the Project level, the parameter will be viewable in the Project.params file, which you can find in the Solution Explorer. If the scope is the Package level, the parameter will be found in the Parameters tab of the package.

12 create parameter

On the Configure Parameters screen, you can alter the details of the parameter. If you click the ellipses button next to the parameter, you can view the parameter details. At the bottom of this window is a check box that says “Required”. A parameter that is Required does not have a default value and must be specified at run. If the parameter is not Required, the parameter does not need to be set for the package to execute.

13 set parameter details

Because I select the Project as the scope of my parameter, I now have a .params file that houses all my project level parameters.

14 project params

If I open this file, I can see the new parameter that was created based on my package configuration.

15 parameter

And if I look at the expression on my connection, I can see the reference to the parameter.

18 parameter referenced by expression

One other really cool thing about 2012 is that objects that have an expression on them now have a little icon indicating the expression on a property of the object. Its about time.

19 expression flag

I hope you found this helpful and that you now have a basic understanding of converting your legacy SSIS packages to 2012. Check out this white paper that has some great information on upgrading to 2012. And if you have any questions or comments, please post them!

Community, SSAS, SSIS, SSRS

SQLRally Voting Opens to the Community

PASS SQLRally 2012 voting is now open! There’s tons of great sessions out there and many of them have been submitted by some of my good friends here at Pragmatic Works. While voting look for great sessions presented by Pragmatic Works experts like

  • Adam Jorgensen
  • Chris Albrektson
  • Gareth Swanepol
  • Dan Clark
  • Brad Schacht
  • and me, too!

Make sure you head over to SQLRally.com and cast your vote! You can vote for as many sessions as you want but you can only vote once, so make your selections wisely! Yours truly has also submitted a few sessions that are currently up for vote! Here are my sessions up for vote and their descriptions.

Zero to Cube – Fast Track to Analytics
This session has been updated for SQL Server 2012. It is consistently a top session from previous events like SQLRally and PASS Summit. Come interact with your speaker and audience like you never have before. This session is 100% demo, packed full of fun and will get you up and running on Analysis Services 2008R2 and 2012 right away!

SSIS For Your Data Warehouse
The ability of SQL Server Integration Services to easily bring together multiple data sources makes it the perfect ETL tool for loading your data warehouse. Loading your data warehouse quickly and efficiently, however, is not always easy. In this session, we’ll explore some advanced techniques for loading your data warehouse and help you overcome some of the learning curve associated with SSIS. The topics discussed will include handling late arriving facts, loading slowly changing dimensions efficiently, and more.

SSIS Unleashed: Expressions and Variables Edition
One of the most powerful aspects of SSIS is the ability to create dynamic and powerful packages utilizing package variables and the SSIS expression language. Unfortunately, the SSIS expression language is also one of the more difficult features to learn regarding SSIS. In this session we will walk through the syntax of the SSIS expression language, common uses for SSIS expressions, as well as some more advanced ways to fully realize the power of SSIS.

Tackling the SSRS Expression Language
The SSRS expression language is one of the most powerful pieces when it comes to creating dynamic reports. Unfortunately, for reporting developers the SSRS expression language is also one of the more difficult features to use regarding SSRS. During this session we will walk through the syntax of the SSRS expression language, how and where to use the expressions, as well as some advanced ways to truly reveal the true power of your reports.

Now that you’re informed, go cast your vote!

Pragmatic Works, SQL Server, SSAS, SSIS, SSRS

BI Documenter 4.2 Released

I’ve been meaning to blog this for a while but since I’ve been on the road for the past month I’ve been pretty busy. This past January, BI Documenter version 4.2 was released by Pragmatic Works. With it comes some pretty cool upgrades and some new functionality.

One of the biggest improvements was the upgrade to the SQL Server Instance Snapshot functionality. It is now possible to include in your documentation Backup Devices, SQL Agent Jobs, and Server Configurations. Personally, I’ve been waiting for the ability to document SQL Agent Jobs so I’m very excited about this release. There’s also many other improvements to the SQL Server Instance Snapshot functionality, so head over to PragmaticWorks.com to check out the other improvements.

Theres also a couple new enhancements that are really nice. Now you have the option to output your documentation in MS Help 2 format. Also, BI Documenter can now document Analysis Services Mining Structures.

With the ability to document SQL Servers, SSIS packages, SSRS reports, and SSAS cubes, its certainly the most encompassing SQL documentation tool out there. If you’re searching for a one stop documentation tool for your SQL Server environment, definitely check out BI Documenter.