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 De...
Read More
This is a very common issue. You develop your package in BIDS/Visual Studio and it runs just fine. Your package also depends on a password for a connection or another key piece of information. When you deploy it to another machine it errors out due to "invalid password" or issue with validation. This is typically a package protection issue. The default package protection level is EncryptSensitiveWithUserKey. To get to this property, while in the Control Flow right click and choose properties. Th...
With the new Project Deployment Model in SSIS developers are gaining the benefit of a new design time only feature called Project Connection Managers. If you’ve developed in SSIS previously Connection Managers require no further detailing, but for those new this is how SSIS connections to data that’s used as a source or destination. This new feature is visible in the Solution Explorer of SSDT (SQL Server Data Tools). Below you will see the traditional method (Package Deployment Model...
The other day I was at a client site, and I was trying to set up my development environment to begin working on some SSIS packages. I was on the next to last restore of 12 separate DB (all told, over 500GB), when the restore failed. Up to this point I hadn?t had any issues at all. Then this sucker popped up: TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.m...
Last week Pragmatic Works launched it?s latest version of BIxPress with some great new features. At the top of that list of new features was adding support for SQL Server 2012 SSIS Packages. The Auditing Framework, Notification Framework, and Snippets you?ve come to rely on in your 2008 and 2005 environments are now available in 2012. Once you?ve tried out the real time monitoring console from BIxPress, it will change the way you monitor packages in your SSIS environment foreve...
This week I was building power pivot models for a client pulling data from Oracle. One table kept giving the below error when trying to import: OLE DB or ODBC error: Accessor is not a parameter accessor.. Out of line object Datasource referring to id ?. Out of line object datasourceview ? temp_dsv Here is the image: None of this error made any sense. It was just a table in Oracle we were trying to pull from. After some troubleshooting I was able to find that a date column was causing the issue. ...
While working with a stacked bar chart in SQL Server Reporting Services (SSRS), I needed to change the sort order for the legend without changing the sort order of the stacked bar. Despite the fact I personally think stacked bar charts are a poor way to visualize data people still want to use them. One of the tricks with a stacked bar chart is that only the bottom value can actually be measured. Thus, we frequently want to force the most important measure to sort to the bottom of the stacked bar...
Earlier this month the latest version of BI xPress was released. There are several new features in this latest release of BI xPress , but one of the coolest is the SSRS Performance Monitoring . The BI xPress SSRS Performance Monitor has four performance monitoring panels. Average Run Time The Average Run Time panel displays the average run times of your report. This is a great place to figure out which reports are taking the longest to execute. This is the place to start if you're trying to figu...
Change data capture is an important consideration when you need to perform an update and an insert in SSIS. Do you need to recognize the change in your ETL or has it been handled previously? Plus doing both insert and update often requires several transforms in addition to your source and destination. Task Factory is an add-in for SQL Server Integration Services that expands the number of sources, destinations, and transforms at your disposal without requiring a script task. One such destination...
When writing T-SQL, like most code, there’s a good way, a bad way, and a short way to do things. Sometimes, the short way and the bad way are synonymous. To assist in being able to write truly ACID (Atomicity, Consistency, Isolation, Durability) Compliant code, one must stick to a few standards in proper commenting and code techniques. Increasing the readability of your code as well will aid in troubleshooting any bugs that pop up, too. First things first, commenting your code is important...
Ever needed to calculate the number of rows inserted every second, for every table in every database on a server? Or, have you ever needed to validate that all processes have stopped writing to tables? These types of questions come up routinely for me. To help with this, I’ve written the following script, which examines metadata values using sys.partitions. This method isn’t as accurate as running SELECT COUNT(*) FROM, but it’s much faster. Keep in mind, since it’s just l...
I often need to know how much free space is available on a drive before and after a cube has been updated. The code below returns a table with Total and Free space per drive. It relies on OLE Automation procedures being configured correctly. SET NOCOUNT ON DECLARE @hr INT DECLARE @fso INT DECLARE @drive CHAR(1) DECLARE @odrive INT DECLARE @TotalSize VARCHAR(20) DECLARE @MB NUMERIC ; SET @MB = 1048576 CREATE TABLE #drives ( drive CHAR(1) PRIMARY KEY, FreeSpace INT NULL , TotalSize INT NULL ) INSE...
The new project deployment model in SSIS 2012 is the new standard for how packages are created, configured and deployed. Any new packages that are created in SSIS will default to using the new deployment model. The most basic way of describing this change is now instead of deploying packages we now deploy projects. Projects get deployed to the new Integration Services Catalog, which I’ll cover in a future post. Also, you get the benefit of project parameters. which allow you to pass values...
When SQL 2012 came out earlier this year, one of the biggest changes to its functionality was how the user deploys his or her Integration Services packages. You still have the option to, from Management Studio, attach it to your Integration Service instance, and of course they can be saved in the file system. However, gone is the SSIS Package Store and in its place is the INTEGRATION SERVICES CATALOG (*insert triumphant war cry*)! Now, some may think, “Hey, it’s just got a new name. ...
Do you want to make 4 thousand dollars a day? Well, I can’t help you with that but I can show you how to enable scale breaks in your SSRS reports. Below I will show you a lesser well known feature within SSRS that only takes 1 click to implement. As you can see from my sample report and data below that Devin and Brian have a significantly different number of fans/followers. Even though this is just sample data I’m sure most of you have had a similar experience when designing reports....
I had a head exploding moment this morning. While trying to install SQL 2012 onto a VM I recently created and installed all of the upgrades to, I realized that I had not allocated enough memory to it. Thinking that I would have to clone my vhd and set it up again, taking hours out of my night tonight, I was loading the bullet into the proverbial chamber. I created a separate vhd that I was just going to attach to the machine to give me more room, but while researching how to get the VM to recogn...
Pragmatic Works has now released version 3.5 of the award-winning BIxPress software ! For those not familiar with BIxPress it’s a tool that helps you develop SSIS/SSAS solutions faster, easily/quickly deploy SSIS packages, monitor performance SSIS packages and much, much more ! So what’s new with 3.5 ? In addition to now having SQL Server 2012 support (SSIS), you may have noticed in previous released that Reporting Services didn’t get much love. That all changes with this relea...
I had an "interesting" problem yesterday. When creating a shared dataset, I made a "noob" error and added a filter so that it would only select rows that had "null" in one of the columns (not knowing at first that you can't filter on Null). Unsurprisingly, when I ran the report, no rows were returned at all. After removing this filter from the shared data set, it still didn't work. My head received enough scratching for the next few months. Now, at this point, if I actually ran the report rather...
I had the pleasure of attending the Jacksonville SQL Server Users Group (JSSUG) meeting last night. The subject was “Indexing your Data Warehouse”, presented by Troy Gallant ( Twitter | Web ). It was a great presentation, and as someone only a couple months experience in the SQL community, very informative. Proper indexing of your data warehouse can help greatly reduce the time and resources it takes to properly query your database. Using clustered and non-clustered indexes, the DBA ...
I found two simple ways to dump MDX query output to a table and then dump the table data to excel file or text file as per need. The two methods are: 1. Using OPENROWSET Syntax : OPENROWSET('provider_name', 'datasource'; 'user_id'; 'password', {[ catalog. ] [ schema. ] object | 'query' }) Connect to the relational server using windows authentication(important!). Example: SELECT * INTO zzz_temp_table FROM OPENROWSET('MSOLAP','DATASOURCE=OLAPServername;Initial Catalog=Cube1_DEV;', 'MDX Query here'...
Many of you may now or in the future have the need to upgrade your SSIS packages to the new SQL Server 2012 SSIS. Luckily, upgrading from SSIS 2005 or 2008 isn’t nearly as painful as you may have experienced upgrading old DTS (SQL Server 2000) packages. If you’re considering the upgrade to 2012 here are some of the new features you have to look forward to: Usability fixes in the development environment (Resolve References, Undo, Zoom, etc…) New/Changed Tasks and Transforms (CD...
I use sharepoint 2010 to display the data analysis tool I have created in excel 2010 to manage the Microsoft licenses as well as to utilise the excel 2010 capabilities of slicers and dicers. This has been going pretty smoothly until yesterday when I stumbled with this error below. "Workbook is larger than the maximum workbook size" So I had to go through the Excel web services properties on the sharepoint server and increase the maximum file size. Here are the steps that I followed. Logged into ...
This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business i...
Another SQL Rally has wrapped up in Dallas, TX and it was great event. My favorite part about these larger events is being able to meet new people and see old friends that I haven’t seen for months. Unfortunately, I did not attend any of the precons this year but I did see a lot of great sessions. I made it a point to at this event to reach outside my comfort zone with the types of sessions I attended. Here’s a wrap up of my couple days in Dallas: Thursday I started my morning by att...
Aggregating over Lookups in SSRS SSRS 2008 R2 has a feature to do a lookup to another dataset, but no way to trivially aggregate the value. It’s a fairly simple solution: Use a small piece of code to do the summing. Now, you may have various LookupSets, so you don’t want to code a function for each, so we store the values in a dictionary lookup. Then when you query at the sum level, you just query the dictionary Dim dValueList As New System.Collections.Generic.Dictionary(Of String, D...