A common question I've been asked a lot lately is how to replace the icon in the Report Manager to my company's logo. It is actually fairly simple to do by following these steps: 1. On the server reporting services is installed on open the folder C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images 2. Rename the file 48folderopen.jpg to anything else (Ex. 48folderopenOLD.jpg) 3. Resize your logo to be 48 x 48 pixels jpg file 4. Rename it 48folderopen.j...
Read More
When developing Reporting Services reports that use Analysis Services as a data source you may find that it is difficult to dynamically set default parameter that are passed into the dataset being used. It turns out this is a lot simpler to do then many people think. In this post I’ll walk you through the steps of setting a default date value in a dataset’s parameter. Step One When creating a dataset that uses Analysis Services you will select Query Designer and then drag over the measures and d...
Here are some steps to fixing an issue I ran into with Window 7 and Reporting Services 2008. The problem was when I attempted to access the Report Server or Report Manager from my browser I received the error HTTP 404 Not Found. I didn’t find a direct answer on the web for this but I did see a lot of people being directed to change the port used to 8080 without any explanation why. I don’t think this is an issue exclusive to Windows 7 but it happened immediately after installing it (maybe someon...
Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel. Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner. With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did: Step One Create a Data Source that points t...
Reports that use an Analysis Services cube as a data source can often have performance problems during pre-execution. Before you even see the classic Report is being generated sign the report is slow to start. The most common reason I’ve found for this performance lag has to do with parameters that are filtering the main dataset. For example, you have a report that uses a sales cube and you want the user to be able to select the date range that the report will return results for. This would mean...
If you've ever had this error "OLE DB error: OLE DB or ODBC error: Operation canceled; HY008." appear while processing your cube you may be a little confused. Here were some of my frustrations with it: First, this error doesn't really give you a single place to start looking. Second, it may produce errors on several attributes that it has nothing to do with. Last, there are plenty of references online asking questions about this error but not a lot of answers. The only clue I had...
There are hundreds of post on this topic but I just ran into this problem recently so I thought I should add one more. I created a package that sends a flat file to an FTP. The package runs fine in BIDS but when you deploy and try and run from a SQL Agent job it fails. Here’s a segment of the error: Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error There’s actually a very simple solution to this problem. By default...
I decided to do a little performance testing on some common SSIS “Tricks” that increase speed and effectiveness of SSIS packages. I wanted to define to what extent just one simple change could make to a package. So in this test I decided to show the difference in using a staging table vs. using the OLE DB Command in a SSIS package that uses a change data capture technique. Many people have the impression that landing data in a staging table is bad for some reason, but in almost...
I had another chance last week to answer an email follow up question from the session Brian and I gave at PASS. This made for a great blog opportunity so I thought I would write about using Fast Parse in SSIS. Hopefully I can explain it even more basic then Microsoft does on MSDN . Using fast parse can drastically improve performance of your package when using a flat files source or data conversion transform by basically not validating the columns that you specify. Keep in mind that these column...
I have decided to make a short series of blogs about possible interview questions to help you prepare for an interview. Last time I wrote about basic DBA questions. Today I have given you some basic SSIS question to study. These basic SSIS questions will give you a starting point on how to prepare for an interview. If you have any other suggestions feel free to add to this list but keep in mind these questions are probably for a mid to junior level BI consultant. I would assume if you're a senio...
This is just a starting point for you to prepare for your interview so make sure not to limit yourself to what you find here. These answers could easily vary but generally someone who is interviewing is looking not only for a correct answer but confidence in your answer. If you have any questions about my answers or have something to add feel free to do so, but I did try to keep the answers short and sweet. SSIS Interview Questions 1. What does a control flow do? A control flow manages the workf...
I wrote a blog about a year ago that gave some tips on basic interview questions that you may run into when interviewing for a SSIS developer job. Well today upon many request I’m writing an advanced version of these questions. Sorry for the delay but I’ve been writing a book , which will be available in July, and most of my free time has been spent on that. So let me know if this is useful and I'll try and come up with another list of questions which hopefully won’t take another year to write. ...
Last week I wrote a set of Advanced SSIS Interview Questions. Here are the answers I came up for these. Of course you may have some variation of what I have and I would love to hear how you would answer these. Feel free to email me how you would answer them. I’ll skip the demonstrate/whiteboard part of the questions. 1. Demonstrate or whiteboard how you would suggest using configuration files in packages. Would you consider it a best practice to create a configuration file for each connection ma...
This is part 1 of my 29 part series called Better Know A SSIS Transform. Hopefully you will find the series informative. I will tell you a little about each transform and follow it up with a demo you can do on your own. Pivoting is a common business practice to gain a better visualization of company performance. Basically the purpose of pivoting is to changing rows into columns. So if you want to display sales across all months you would use pivoting to turn a single date column with the month i...
Checkpoints are a great tool in SSIS that many developers go years without even experimenting with. I hope to enlighten you on what Checkpoints are and why it is beneficial to use them. Also, I will walk you through a basic example package where they have been implemented. What does it do? With Checkpoints enabled on a package it will save the state of the package as it moves through each step or task and place it in a XML file upon failure of the package. If your package does fail you can corre...
This is part 2 of my 29 part series called Better Know A SSIS Transform. Hopefully you will find the series informative. I will tell you a little about each transform and follow it up with a demo basic you can do on your own. When developing SSIS packages you are bound find out that some transforms require your data to be sorted before it can accept new input rows (Ex. Merge & Merge Join). The Sort Transform may be one of the more frequently used transforms for that reason, but it really sho...
Using a Deployment Manifest in SSIS allows you to deploy a set of packages to a target location using a wizard for installing your packages. The benefit to using it is the nice user interface that a wizard provides. The disadvantage of using it is it’s lack of flexibility. When using the Deployment Manifest keep in mind that it’s an all or nothing deployment, which means you cannot choose to just deploy one or two packages at a time from your SSIS project. Using the Deployment Manifest will depl...
Recently I decided to expand my coding knowledge and picked up a Visual Basic book. I’ve been working in this new book for about two weeks when I opened Visual Studio this morning and all my VB templates had disappeared. Of course, it turned out to be a common problem and immediately found the answer so I thought I’d just pass it along. Make sure that Visual Studio is not open and bring up a command prompt Navigate to the Visual Studio executable (devenv.exe) is. In my ca...
This is part 3 of my 29 part series called Better Know A SSIS Transform. Hopefully you will find the series informative. I will tell you a little about each transform and follow it up with a demo basic you can do on your own. The Conditional Split provides a way to evaluate incoming rows and separate those rows by an expression your design. After these rows are separated they are sent to different outputs so they can either be cleansed, loaded separately, or detect changing data (a good substitu...
For those that are new to SSIS, using expressions may seem very foreign and unpleasant. Not only is it a new language for you to learn but new data types to understand as well. If you can get past this unwelcoming feelings you’ll realize using expressions is a great method for making your SSIS packages dynamic. I will tell you about the basics you need to know before starting to use expressions. If you’re interested in a crash course on writing expressions read this White Paper by Brian Knight ....
Are you tired of the same old Microsoft news feed when you open Visual Studio (BIDS)? Why not go ahead and update it so you every time you load VS you get news that you’re actually interested in. I’ve changed my news feed to display all the SQL Server Central blogs so now instead of ignoring the news feed I actually read through each. I’m sure many people have blogged about this before but I want to give credit to Scott Gleason for showing me this nice feature several months ago. 1. Open Visual ...
While in the process of developing SSIS packages it’s likely you will deal with errors occasionally unless you’re the Superstar developer that never makes mistakes. Of course your first steps to deciphering the problem is to look at either the progress/execution results tab in BIDS. The problem with viewing error messages here is that more than likely the error you receive does not fit on the single line and goes off the screen. So what do you do? Right-click select Copy Message Text and paste i...
A very common need in SSIS is to check to see if a file exist before you run what could be a very long process in your package. There are no native tasks inside SSIS that can do this check but you can accomplish this using a Script Task. Here are the steps to check to see if a file exist. 1. Setup two variables. The variable strFileLocation has a string data type with the value being the location of the file I want to check for. The variable bolFileExists has a boolean data type with the value c...
Opening DTS packages on a 2008 SQL Server requires several steps to be able to view the packages in a DTS designer. Here’s a link to these steps on a 32-bit machine or on a 64-bit machine . After following these step you will be able to open your DTS packages in the DTS Designer. These steps are great for opening DTS packages but I ran into a problem recently where I was not able to import packages on my server which shouldn’t require as many prerequisites like opening packages does. When I atte...
I recently worked on a project that involved loading a Data Warehouse from a DB2 source. In this project we used the Microsoft OLE DB Provider for DB2 Data Provider for the ETL process in SSIS. Unfortunately, one of the limitations to using this Data Provider is the the OLE DB Source does not allow you to pass in parameters which I do frequently in Data Warehouses for incremental loading. The work around I did for this was to store the query I wanted to use in my source in a table on the SQL Ser...