All posts by Dustin Ryan

Dustin is a Data Platform Solutions Architect on the Education team at Microsoft. You can find Dustin blogging and speaking at events like SQL Saturday, Code Camp, and SQL Rally. Follow Dustin on Twitter @SQLDusty.

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.

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

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.

The Tool Any Serious SSIS Developer Shouldn’t Be Without

If you’re not familiar with the BIDS plugin by Pragmatic Works called BI xPress, you really should check out this tool. For the small amount of money you’ll spend on BI xPress, you’ll add a ton of powerful weapons in your SSIS/SSAS development arsenal that no serious developer should be without. Since I’ve been working for Pragmatic Works for the past couple years I’ve had many opportunities to work with BI xPress. I can definitively say without a doubt that the gigs I’ve been on where I have BI xPress as a development tool go much much faster simply because of the additional weapons in my SSIS development arsenal. I’ve blogged this before, but I just wanted to take a couple paragraphs to talk about my all time favorite BI xPress feature, as well as the newest BI xPress feature.

My number one favorite feature of BI xPress is the Package Builder Wizard. In my opinion, this is probably the most powerful development tool in BI xPress. The reason the BI xPress Package Builder Wizard is so powerful is simply because it allows you to speed up your package development.

Capture

Using the Package Builder Wizard, a developer/development team can develop and manage package templates. So imagine you have a couple senior SSIS developers on your team and you also have a couple junior SSIS developers on your team. The senior SSIS developer can develop package templates with the more complex pieces of the package already developed and then pass those on to the junior guys. I’ve also seen the senior team members develop package templates with the correct naming conventions, necessary variables and connections, and annotations. These templates are then used as a starting point for the main development team, which saves the developers a great deal of time.

One of the great things about the Package Builder Wizard (its also my personal favorite feature) is the ability to alter existing packages with a prebuilt template. I once did some work for a client that needed to add a connection manager to an auditing database, event handler logic, and package configurations to 100 or so packages. Without BI xPress, this would have taken a long time. I don’t even want to imagine how long it would have taken. But without BI xPress, this only took me less than an hour (not counting testing 😉 ). I developed a template which included all the necessary variables, connection manager, event handler tasks, and package configurations. I then applied that single template to all of the packages in my project. It was literally that easy. Needless to say the client was pretty excited that it only took a few minutes to get through the work, at which point they then instructed me not to tell anyone I had completed the work that quickly :).

The newest feature of BI xPress is the MDX Calculation Builder. In short, the MDX Calculation Builder allows you to quickly build MDX calculations without having to script them out by hand. Do you need to script in YTD, QTD, MTD calculations? The BI xPress Calculation Builder can do it in 3 steps. Do you need to calculate the percent of a parent, compare a measure to a parallel period, or calculate an average? The calculation builder can do all that, too, all without manual scripting of MDX. It can even build dynamic sets for you.

Capture2

There are tons of other feature in BI xPress that I haven’t even mentioned that make BI xPress really just an insane tool no serious SSIS developer should be without. And since it uses all native SSIS components, the only place BI xPress needs to be installed is on the machine where development takes place. If you haven’t checked out BI xPress, I highly suggest you download the free trial and just give it a shot.

MDX Puzzles: Super 8–Solution

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

Then finally we’ll reference these to calculated measures in our final select statement.

WITH 
MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

MDX Puzzles: Super 8

If you’ve followed my blog over on BIDN.com, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out. As always, these MDX queries were written against the Adventure Works DW. Here are the requirements:

Rows:

  • Calendar Year

Columns:

  • Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist)
  • First Date in Year

Hints:

  • Take a gander at this

And here’s a sneak peak at my results:

QueryResults

If you think you’ve got the correct answer, send your query to

 email me

Of all the correct answers I get, I’ll pick one at the end of next week (April 29) and send out one of these really cool shirts, so good luck, peeps!

KickinSSAStshirts

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!