I attended the 2010 Jacksonville Code Camp Sataurday and really was impressed with Pragmatic Works. The entire Business Intelligence stack was covered by speakers from PW including Brian Knight, Adam Jorgenson, Mike Davis, Devin Knight, Dustin Ryan, Brad Schacht and Brian McDonald . You can regularly check out each of their blogs here on BIDN. Even though it was a .NET Camp the BI meeting rooms were filled and had standing room only for some of the sessions. Give kudos to the organizers of the C...
Read More
One of the hardest things about starting a new project is getting to know the database with which you are working. Here is a quick way to make a list of the all columns along with the name of the corresponding table within the database. Copy and Paste the code in a query window and try it. SELECT COLUMN_NAME , TABLE_NAME FROM INFORMATION_SCHEMA . COLUMNS ORDER BY 1 Sorting by 1 (COLUMN_NAME) allows you quickly see which columns are common to tables in the database and might be the ones you will ...
You might have an occasion when you want to conditionally keep a parameter from passing through to a drill-down report. The Omit expression is where you would do this. The Omit expression needs to evaluate to “True” to prevent passing the parameter to the drill-down report. When you first click on the Omit icon, the default value is “False”. Simply enter an appropriate expression that will properly evaluate at run time. Remember that “False” means the parameter will pass through to the drill-dow...
Here’s an error I recently got in SSRS when previewing a report: The Value of the Color property for the textrun 'Textbox19.Paragraphs[0].TextRuns[0]' is #00ffffff, which is not a valid Color. I was changing the color scheme of several items in the report and inadvertently selected No color instead of White on one of them. The report looks fine in Design but produces the error in Preview. Since I had several text boxes I went to the Properties Window and selected the one in the error message (in...
It turns out that the biggest value you can use as an integer parameter in SSRS is 2,147,483,647. Any value greater will produce an error stating, “The value provided for the report parameter…is not valid for its type.” This is because the Parameter Data Type Integer is literally that – an Integer can be up 2,147,483,647. If you have values greater than the magic number of 2,147,483,647 (in other words a BigInt), you will have to change the Report Parameter Data Type to Text. Now the report runs...
Sometimes when you have a really complex expression in an SSIS derived column, it is easy to miss something simple and spend too much time trying to figure it. Just when you think you have corrected the expression, another error keeps it from validating. If you are sure you have the logic figured out but the expression is still “red” try this tip. Start a new Derived Column to start re-creating the same expression with which you're having trouble. Make sure you work from left to right and drag e...
It’s too bad there isn’t an IsNumeric Expression that can be used in a derived column to check and see if a string value is numeric in SSIS. In converting a DTS package to SSIS I ran across this script that needed to be converted. Here’s how the old DTS script looked: IF IsNumeric( DTSSource("Col049")) Then DTSDestination("InsDT") = "*" Else DTSDestination("InsDT") = " " End IF First, drag a Script Component into the Data Flow, selecting Transformation as the Type. Click OK and insert into Data ...
There are a few circumstances in SSIS that you need to do some things that don’t really have a good explanation but are necessary to keep BIDS happy. For example, if you use the NULL function when creating a derived column, you must also CAST it even though it seems that you have already told it the data type within the NULL function. In this example I want to set Column1 equal to a NULL string if it is an empty string. Otherwise I want it to equal Column2. Bad expression: Column1 == "" ? NULL(D...
I recently converted a package from DTS to SSIS that had an OLEDB connection to an AS400. There wasn’t an OLEDB driver available in the environment so I ended up having to use a Data Reader with an ADO.net connection. Here are the steps if you ever need to do the same: First, copy the SQL query from the unusable OLEDB source task. Next, right click the Connection Manager area to create a new ADO.NET Connection. Configure an ADO.net connection based on the previously defined AS400 connection. The...
I was minding my own business when all of a sudden I ran into a Derived Column that needed to be a combination of three string columns which held YEAR, MONTH and DAY. MONTH and DAY were both 2 characters long but the YEAR column was 3 characters long. As it turns out it was a leftover from pre-Y2K where 101 = year 2001, 102 = year 2002, etc. No problem right? Just add 1900 to the year and concatenate MONTH + ”/” + DAY + “/” + (YEAR+1900) and Cast as a Date. First, the YEAR must be cast as a numb...
I have been migrating DTS packages to SSIS this week and have had to learn the hard way but also the fun way about the importance of several aspects of using Configuration Files. If you read through the MSDN Library (that was the fun part) you will come across this statement referring to the Reuse or Overwrite option. It simply says, “ Make sure you understand this behavior, as choosing the wrong option could change the content of the configuration file and produce unexpected execution results.”...
I needed to create a report to display data in a Table and a Chart side by side on each page for multiple groups of records. This was easily done by putting a List object on the page and then “nesting” the Table and the Chart inside the default cell which acts like a Rectangle container. Then all I had to do was create a Row Group for the List object and put a page break between each instance of a group. Now my only concern is that my color scheme is too drab even though it does match the compan...
I had a chart that wasn’t displaying the results that I had worked so hard in my stored procedure to get just like I wanted. The report was not showing each year on the bottom axis of the graph so the user couldn’t compare the results with the year by year results in the spreadsheet below. Here’s how I fixed it. I right-clicked the category axis and selected Axis Properties. This is not always easy to get to because some of the chart items overlap. You can make sure you’re on the correct item by...
Adam put me on to this free eBook : Dissecting SQL Server Execution Plans by Grant Fritchey I put it here to save the link for reference and to share with anyone interested. http://www.red-gate.com/specials/grant.htm When you get to the site you can sign up for other stuff by giving your email or click on the Just the Ebook please link.
Thanks to Devin and Dustin I got the answer to my post about how to have a single drop down to select a date range for the 2 parameters of StartDate and EndDate. Here is a little more explanation that shows what I did. The table that holds the dates looks like this: AcctPeriod BeginDate EndDate 1.0 2010-01-01 2010-01-22 2.0 2010-01-23 2010-02-19 3.0 2010-02-20 2010-03-19 4.0 2010-03-20 2010-04-05 4.1 2010-04-06 2010-04-23 4.5 2010-04-24 2010-04-30 First, I changed the SELECT part of the stored p...
After running into the problem of dividing by zero is SSRS, it was once again a mighty expression that saved the day…or at least partly. You can read Dustin’s Blog to see how to work around the problem. =iif(sum(Fields!Beta.Value)=0,0,sum(Fields!Alpha.Value)/iif(sum(Fields!Beta.Value)=0,1,sum(Fields!Beta.Value)) The thing is that I really wanted to get something beside 0 percent when the value in the divisor was zero. Anyway, what percent of 0 is 0? Great minds want to know. I decided to figure ...
I'm fairly new to Visual Studio so I like learning neat little "tricks" every now and then as I work with it. Here's a tip that might come in handy some time. If you have created a bunch of projects, say maybe for testing or something like that, you could end up with some of them listed on your Start Page. You may want to limit the number of recent projects that show up there. To do this, go to Options – Tools – Environment – General and under recent files put a number that corresponds to the nu...
To make my client's report easier to understand it needed to display a calculated number followed by some text all within one cell. The CStr function was good enough to convert the number to text but the format of the number was not correct. A comma separator for the 1,000’s was needed. The FormatNumber function took care of this but I also had to include 0 for the NumDigitsAfterDecimal argument since this was a count. Optionally you could specify using parentheses for negative numbers and add l...
My client wants a report to be exported to Excel. The spreadsheet shows up with what seems to be miscellaneous empty cells when it is exported from SSRS. As it turns out the explanation is pretty simple but I’m not sure there is an easy fix. It has to do with two issues. First, Excel will interpret each object (textbox, image, etc.) and try to match it to a column in the tablix. If the object doesn’t exactly line up with the intended column Excel will create another column. Second, if you merge ...
My client had a desire to show that some numbers were “NULL” instead of either blank or zero (0) when they appeared on a particular report. I looked for a check box that might handle this but no thanks to SSRS I had to create an expression using the IsNumeric Function. Check it out: iif(IsNumeric(Fields!Quantity.Value),Fields!Quantity.Value,”NULL”) If the Quantity is a number, it returns the number. Otherwise the word “NULL” is returned.
So I finally got through all the SSIS Class Labs including the Group Labs and decided to figure out why I didn't have AdventureWorks2008. I had previously tried to enable FILESTREAM according to some blogs I had read about it but had no success. Maybe I just understand all this a little better now but when I exactly followed these steps right out of the help file it worked for me: 1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and t...
I was going back through a project and cleaning up a few things (you know…trying to use best practices, etc.) and got this error: Error: Failed to lock variable "Dts.Variables("intAuditRowsInserted").Value = 0" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the vari...
Yesterday was my first day to actually do a project with SSIS that means something in the real world. We had a deadline to meet and needed to extract part of the data from several files, combine the needed info into one file and make sure that there was no duplication in the final list. SSIS made it quick and easy. All that was needed was a Data Flow Task that took the Flat File Source and extracted the appropriate data and put it into a new File Destination. This action was repeated automatical...
This morning I got up early (since I work midnight - but only 3 more days...sweet) and listened in on the Pragmatic Works Webinar with Jorge Segarra. Check it out at http://www.pragmaticworks.com/resources/webinars/ . This was the first of a series designed for beginners. One subject that was covered was Options that should be selected under Database Properties. Recovery Model should be set to Simple as opposed to Full or Bulk-logged and Auto Close and Auto Shrink should be set to False. Obvious...
Have you ever worked on a query and believed it was perfect but you keep getting error messages and so you tweak it again and fix what you think might be wrong only to get another error message. I know this is going to sound stupid but that happened to me. Next time go ahead and READ the error message. If it include something like this: Could not find... Make sure you are using the correct DB. You probably don't want “master” in the Available Databases drop-down box so select the correct one and...