Once upon a time, in SQL Server 2005, the SQL Server team was working tightly with the SQL Server product team to reserve a special spot of shared memory for SQL Server for SSIS to dump into and then as SQL Server would write into the database seamlessly. This was done with the with the SQL Server Destination in SSIS' data flow. The net result in SQL Server 2005 was about a 8% gain in performance typically in my older benchmarks. Enter SQL Server 2005 SP2 stage right. The big tenant of SQL Serve...
Read More
We had a bit of an impromptu session today during today's webinar. You can find the recording for that session at http://www.PragmaticWorks.com and click Webinars. Here's my scripted demo which shows DAX, PowerPivot and more from the AdventureWorksDW2008(R2 optionaly) database. You can also download the weather data from our friends at PowerPivotPro ( http://powerpivotpro.com/2009/10/28/us-meteorological-data/ ). With those two pieces, this script should work great for your presentation! Please ...
For those who attended the webinar last week, I mentioned some code on how to do a parallel load in SSIS. I started to write this in a blog post but it was so long, that I had to make an article out of it. You'll find the full post here: http://bidn.com/articles/integration-services/119/parallel-loads-in-ssis .
April Webinar: MVP Series In these sessions you will learn how to design a data warehouse, load it using SSIS, place a cube on top of it using SSAS and report against it using SSRS. Finally, you'll see how to performance tune and scale the data warehouse using common techniques. All speakers have authored many of your favorite books from Wrox. All sessions are recorded in case you miss the date. Please be sure to install gotomeeting video codec’s , those unable to play webinar recordings should ...
I've been using SQL Server 2008 R2 in beta for about 6 months with no issues but today, I had a strange one I'd have to share. In Management Studio, I was adjusting my open windows and at some point Object Explorer closed and would not come back. I was able to get in back in Full Screen only. It turns out that in my registry a key had become corrupt. The easy way to fix it though was to go to Window ---> Reset Window Layout in Managment Studio. After that, I was golden. Well, at least after a...
February SQL Server 101 Series New to SQL Server or trying to enter the field? This webinar series will walk you through the Microsoft SQL Server stack so that you can become a DBA or Business Intelligence (BI) developer. Registration Opens February 1st, 2010 and is limited to the first 1,000 people. Webinars traditionally fill quickly. Date and Time (All times EST) All sessions are free. Session and Speaker * (click book images take you to author book) Description Febuary 22 9AM View Recording ...
I wanted to post this letter that just went out from PASS. As it reads, Andy, Steve and I are excited to announce that we have gifted one of our prized brands, SQL Saturday to PASS to grow the brand with the community owning at a whole. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 Dear Community Leaders, It gives me tremendous pleasure to announce that the PASS Board of Directors recently voted to acquire the SQL Saturday Event brand and management. SQL Saturday is a...
Thanks for all who attended my SQL Lunch session! There was a few questions that reoccured. One was about the free components I showed. The free component was Task Factory and can be downloaded from http://www.pragmaticworks.com. The other was for the coding examples, which you can see here: Download the code examples !
Often times, you want to analyze sales using a year to date view as shown below: While you can do this in an MDX formula in SSRS, you might find this calculation valuable enough to store a server-side calculation. To do this, you'll need to pick what date dimension or role playing dimension you want to slice this by. After you know that, you can use an MDX calculation as shown below for this: Sum(YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]) This will show a r...
We're moving fast at the BI Developer Network (BIDN.com). In an effort to make sure you get your BI questions answered, we've installed a new twitter interface that if your question in our forums doesn't go answered after 2 days, it will escallated in a number of ways. We'll first feature your question in our newsletter and it will also be automatically be tweeted to http://www.twitter.com/bidn for any followers. Your question will be featured on twitter with a prefix of "BIDN User SOS" with you...
I've been working this week to automate the BIDN.com's twitter connectivity and to do so, decided to build a quick Twitter interface to communicate with BIDN. I needed a simple console application that could be called via a trigger or a SQL Agent Job (my actual method). The entire application winds up only being a 50 lines of code or so. Essentially from a command line, you pass in your twitter account, password, the message and optional URL. If you pass a URL in, it will be converted to TinyURL...
As ETL developers, we typically lump anything that's NULL or something we can't find an ETL lookup match on to -1 or "unknown". We would first of course have to create an Unknown record in each dimension table then during the ETL attempt an INNER JOIN into the dimension table through a Lookup Transform or through a classic INNER JOIN. If a match could not be found, then it's branded as unknown or -1. On a recent project, a new need for unknown arose which I've seen in the past. This hardened my ...
After promising a dozen people a dozen times that I'd do this, I finally posted my MDX examples that use during my sessions http://www.bidn.com/files/ . You'll need to have the AdventureWorks cube installed, which you can get from the CodePlex site here: http://www.codeplex.com/MSFTDBProdSamples . Once deployed, open Management Studio and open the MDX Example.mdx code. Each block of code is commented telling you why you're doing what you're doing. Make sure you go off script though and experimen...
I just stumbled upon a great resource for Microsoft BI believe it or not, on the Microsoft website. The first one is a list of short concise videos about how to implmement BI: http://www.microsoft.com/events/series/bi.aspx?tab=videos On the same site, you can also find a list of webcasts that are much longer (about an hour each) that offer a complete view of BI topic: http://www.microsoft.com/events/series/bi.aspx?tab=webcasts Don't be fooled by the original lists of webcasts. Click each of the ...
I'm excited to announce the new version of BI xPress 2.0. It has a new feature called a Package Builder. The Package Builder will allow you to capture components of SSIS into a template that you can reuse and deploy to hundreds of packages in seconds. You can also use some of the Package Builder to create a new package with one of our canned templates. This is one of many features in the new build! To see the recording of the launch event click the below link: https://www1.gotomeeting.com/regist...
In the last post ( http://blogs.pragmaticworks.com/brian_knight/2009/09/calling-an-external-query-from-mdx.html ), I showed you how to install an external assembly for Analysis Services to query a relational data set, whether in SQL Server or any other database like DB2. This enables you to close the loop as a user is analyzing the data to see live real-time data. Now that you have that installed, let’s create an action to open the recordset with a right-click action in Excel or most cube...
Recently, we had a client requirement to show SQL Server and DB2 data inside of a Excel Analysis Service pivot table. They didn’t want to create dozens of drill-through reports for the users to slice, sort and filter any number of ways and they wanted to tie the cube data to real-time OLTP data. For example, they wanted to look at a customer and then see an action that allows the user to see all the open orders for that customer. To do that, we implemented a rowset action in Analysis Serv...
Whether you’ve been involved with SQL Server Integration Services (SSIS) for years or are just learning SSIS, you’ve probably found the learning curve of the SSIS expression language quite challenging. The expression language is a core component of SSIS that helps users make any component in an SSIS packages dynamic and also helps the data flow make workflow decisions. This whitepaper walks you through a crash course in the SSIS expression language across all components of SSIS. Afte...
Again we’ve learned that combining alcohol and SSIS training videos is a very bad combination. We’ve now launch a new series of fun videos that teach you SSIS in a very interesting way. Let us know what you think as we’re not sure we hit the mark with them :) The series features one of our developers, Dustin Ryan showing you SSIS through a very irate way. You may recognize the voice of the boss :). Of course this was meant to be funny so please take it in its proper way. Dustin is normally a muc...
One of the specialties of Pragmatic Works is upgrading DTS packages to SSIS but we were recently quite frustrated when we tried to use the advertised DTS Designer inside SQL Server 2008. The error you’ll receive is the following (even after you install the components). SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI) We fou...
One of the frustrating things about the new Visual Studio interface is the load speed. This applies to SQL Server 2005 as well. I have a very new computer which is quite speedy and the splash screen still takes 5-10 seconds to load. On my old machine, this is more like 10-15 seconds while the splash screen is loaded before SQL Server Management Studio would show the login dialog box. A remedy I stumbled across was to add the -nosplash switch at the end of the executables call in the shortcut. Af...
Many already know this but I was speaking to a class this week where no one knew it and thought I'd post it just in case. While in Management Studio one person asked me if there's a way to set a value to NULL while viewing and editing a table. Just in case you didn't know this, you can set a cell to NULL in the table data editor by typing CTL-0. Hopefully, that saves you some time from having to leave the editor to write a quick query! Brian Knight
This is another common question we face many times "Should I store my SSIS Packages to File System or on SQL Server?" There are two places you can store your packages: on the file system or in the msdb database. Each storage option has its own pros and cons and which option you choose will be based on what is more important to you. We’ll cover these pros and cons in much depth in this section but to summarize, the following table gives you a high-level idea of which storage option is b...
SSIS Improved Send Mail Task by adding support of SMTP but still it lakes most common email requirement which is send email in HTML format. Send Mail Task in SSIS only support text format so again programmers have to use script task and write some VB.net code to send email in HTML format. You can use the following reusable routine to send HTML formatted email. Send HTML email '// Example :
Generally developers use Scripting.FileSystem object in ActiveX script task to perform various file system related tasks (e.g. Copy file, Delete file etc.). In SSIS you can perform many file system related common tasks using File System task . You should find out the possibility of replacing ActiveX script using SSIS File System Task . If any task you performing using Scripting.FileSystem is not possible using File System Task (i.e. Check File Exists) then you can use Script Task in SSIS and use...