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.
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 
. If you’re not running SQL Server 2008, obviously the BCP isn’t going to be located under the 100 folder.
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!
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.
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.
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:
If you think you’ve got the correct answer, send your query to
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!
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!
Recursive Queries and The Power of Common Table Expressions
Yesterday I was working on a requirement for a client that asked me to show a list of Organizational Units and the every single parent Organization above every Organization. The source table gave each Org Unit’s ID with their parent Org Unit’s ID. In Oracle’s PSQL, this requirement was met using the Start With and Connect By statements. In TSQL, however, there is no such thing. To meet this requirement, I could have written multiple select statements Union’ed together with differing amounts of joins back to the OrgUnit table to go up in the OrgUnit hierarchy multiple levels, but this would have been extremely painful, time consuming, and the query would have performed very poorly. Instead, I used the magic of Common Table Expressions (CTE) to build a simple query with a recursive join.
If you’re not familiar with CTE’s, check out this link about using Common Table Expressions.
For this example, I’m using the DimEmployee table in the AdventureWorksDW database. So let’s take a look at the query and then we’ll discuss it.
;with a as ( /*This select statement is our anchor statement. This will bring back our top level employees that do not report to anyone.*/ Select EmployeeKey, /*If the ParentEmployeeKey IS NULL, then we want to set the EmployeeKey as the ParentEmployeeKey*/ CASE WHEN ParentEmployeeKey IS NULL THEN EmployeeKey ELSE ParentEmployeeKey END as ParentEmployeeKey, FirstName + ' ' + LastName + ', ' + Title as Boss, FirstName + ' ' + LastName + ', ' + Title as Employee, 0 as [Level] From DimEmployee where ParentEmployeeKey is null Union All /*This select statement joins our source table back to our CTE using the ParentEmployeeKey.*/ Select e.EmployeeKey, e.ParentEmployeeKey, a.Employee as Boss, e.FirstName + ' ' + e.LastName + ', ' + e.Title, [Level] + 1 as [Level] From DimEmployee e /*This is our recursive join back to our CTE.*/ inner join a on a.EmployeeKey = e.ParentEmployeeKey ) Select * From a
The first thing you’ve probably noticed is that my CTE contains two select statements union’ed together. The first select statement is whats called our anchor statement. The first select statement only brings back our top level employees that do not report to anyone. In the case of AdventureWorksDW, there is only one top level employee (Ken Sanchez, CEO), but your situation could have multiple top levels.
The second select statement returns all employees, but it does something very interesting. The select statement actually join DimEmployee to the CTE it is contained in, thus creating a recursive join. By joining DimEmployee.ParentEmployeeKey to the EmployeeKey in the CTE, we are able to recursively join our results back to itself over and over until no records with a ParentEmployeeKey are left.
But if you remember, my requirements were to show every ParentEmployeeKey in the hierarchy of each Employee. So our first CTE alone isn’t going to cut it. To get all ParentEmployeeKey’s in the hierarchy, we will need a second CTE with a recursive join. Here is the select statement that returns those results.
;with a as ( /*This select statement is our anchor statement. This will bring back our top level employees that do not report to anyone.*/ Select EmployeeKey, /*If the ParentEmployeeKey IS NULL, then we want to set the EmployeeKey as the ParentEmployeeKey*/ CASE WHEN ParentEmployeeKey IS NULL THEN EmployeeKey ELSE ParentEmployeeKey END as ParentEmployeeKey, FirstName + ' ' + LastName + ', ' + Title as Boss, FirstName + ' ' + LastName + ', ' + Title as Employee, 0 as [Level] From DimEmployee where ParentEmployeeKey is null Union All /*This select statement joins our source table back to our CTE using the ParentEmployeeKey.*/ Select e.EmployeeKey, e.ParentEmployeeKey, a.Employee as Boss, e.FirstName + ' ' + e.LastName + ', ' + e.Title, [Level] + 1 as [Level] From DimEmployee e /*This is our recursive join back to our CTE.*/ inner join a on a.EmployeeKey = e.ParentEmployeeKey ) , b as ( /*Once again this is our anchor statement. We are only bringing in Employees who report to someone besides themselves.*/ Select EmployeeKey, ParentEmployeeKey, Boss, Employee, [Level] From a Where ParentEmployeeKey != EmployeeKey UNION ALL /* This is where it gets tricky. In this recursive join, we join on the ParentEmployeeKey's in CTE a with the EmployeeKeys in this CTE, but we display the ParentEmployeeKey of this CTE. This allows us to show the parent of the parent recursively. Essentially we are going back up the hierarchy to display the boss of each level in the hierarchy.*/ Select a.EmployeeKey, b.ParentEmployeeKey, b.Boss, a.Employee, b.Level From a inner join b on a.ParentEmployeeKey = b.EmployeeKey ) /* In the final select statement, we must once again query our first CTE, a, to gather the employee that do no report to anyone. Those employees are at level 0. */ Select EmployeeKey, ParentEmployeeKey, Boss, Employee, [Level] as LevelsDownFromTop From a Where [Level] = 0 union all /* Then we query CTE b to get all the other employees along with every employee above them in their hierarchy. */ Select * From b Order by employeekey, [Level]
Essentially what we are doing in our second CTE, called “b”, is we are joining on the ParentEmployeeKey’s in the first CTE, “a”, with the EmployeeKey in b. This allows us to show the parent of the parent so we can go up the hierarchy and return all the bosses in an employee’s hierarchy. Here is a screen shot of some of the results.
As you can see, the third record shows that Guy Gilbert reports to Jo Brown. One level up from Jo Brown, we see that Guy Gilbert reports to Peter Krebs, who is Jo Brown’s boss. And finally we can see that Guy also reports to Ken Sanchez, since Ken is the CEO.
SSAS 2008 Bug: Normal Additive Measures and Semi Additive Measures in the Same Measure Group Don’t Display Properly
Devin Knight and I stumbled onto another weird SSAS bug today. We had a measure group that had two measures. The first measure was semi additive (Last Non Empty) and the second measure was a normal additive type (Sum). The additive measure would not display along side the semi additive measure, whether I browsed the cube in Excel, BIDS, SSMS, or wrote an MDX query.
Well as it turns out this is a known issue that was identified some time ago. SQL Server 2008 SP1 solves this issue: http://support.microsoft.com/kb/969099 .
So if you’re ever on site working in an environment you are unfamiliar with and you experience goofy behavior where semi additive and additive measures in the same measure group do not display properly, check the SQL Server AS version! The version to look out for is 10.0.1600.
SSAS 2008 Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”
While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked perfect, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error:
After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),
my calculations appeared to be working correctly. Apparently this is some kind of bug in the cube browser in SSMS and BIDS. You’ll notice that if you connect to your cube from Excel and browse the cube using a Pivot Table, you are able to successfully browse the cube without any strange errors. I hope this saves someone else the 45 minutes I spent trying to figure out what in the world was going on.
Greetings, SQL World!
As my first post on my new blog site, I figured I’d give a bit of an introduction. I’m a business intelligence consultant with the Pragmatic Works crew in Jacksonville, Florida. Lately I’ve been creating BI solutions utilizing SSRS, SSIS, SSAS, and SharePoint. My experience stretches through a variety of industries such as health care, manufacturing, transportation, retail, and insurance, just to name a few.
My primary purpose for this blog is to discuss SQL and BI related topics and to interact with the professional SQL community. I look forward to making new friends, learning lots of new things, and sharing my experiences!
You can find me writing here on my blog, consulting with any of the many clients of Pragmatic Works, or tweeting @SQLDusty.