Mike Davis

MikeDavis : Most Recent postings

Don't miss a thing - Get free updates by RSS

• SSIS Renaming and Moving Tasks
  If you are like me you click the mouse a lot, and fast. I drive my mouse to the breaking point clicking around so fast on items, especially in SSIS, which is a graphical interface. But when clicking on the tasks in SSIS I sometimes end up in the rename function instead of opening the edit screen. There is an easy way to avoid this. Look at the image below: Double clicking in the red section will open the editor. Double clicking slowly in the blue section might start the rename function. If you d...
  SSIS UI Click Rename Move
  3/4/2010  by MikeDavis  Comments: 4
• SSIS Excute SQL Task Fails with Executing the Query " " Failed
  When running a package with an execute SQL Task it is easy to get syntax errors. If the tasks turns red, but still accomplished its work, like an insert statement and the error you recieve is: Executing the Query " " Failed ...  This is saying the query was blank. If you have a GO statement at the bottom of your SQL task an there is a space after it, that is the cause of your issue. Remove the space, or the entire Go statement.
  SSIS SQL Task Error Query Failed Blank GO
  3/2/2010  by MikeDavis  Comments: 0
• SSRS 2008 Report Model with no Foreign Key and/or primary Key
  When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, One with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table.    In visual studio I created a report model project and selected the database with these two tables....
  SSRS 2008 Report Model NO Foreign Key NO primary Key
  2/26/2010  by MikeDavis  Comments: 0
• SSRS 2008 Change the Series Colors for Chart and Legend
  In my last SSRS 2008 class I was showing the students the chart tools and used color as a series on a chart. It was the product color column from the AdventureWorks 2008 Production.Product table. The series automatically chooses colors based on the color pallete of the chart. This made the report confusing because the colors did not match the product colors.    One of my students asked if the expression could be used to change the color. So I tried and it worked. I placed the color f...
  SSRS 2008 Change Series Colors Chart Legend
  2/25/2010  by MikeDavis  Comments: 0
• SSRS Expressions MTD Month to Date
  If you have a report in SQL server reporting services that has start and end date parameters, you may want to have default dates to show the current month to date. The end date is easy, it is just the today() function. The start date needs to get the first day of the month, there are several ways accomplish this. Here are two code snippets that do this:   =dateadd( "d", 1-datepart("d", today() ), today() ) or   =DateSerial( year(today()), month(today()), 1) The first example subtracts the number...
  
  2/24/2010  by MikeDavis  Comments: 0
• SSIS 2008 Setting proper case with a Script Component
  There is a command in VB that can be used to character data to proper case. This is the first letter of each word is capitalized and the rest is lower case. This is a great way to clean up a data base or is great to use when loading a data warehouse. Drag in a script component into a data flow and check the input files you want to use as ReadWrite. The below examples are on the FirstName column.   Here is the VB Code:   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' Row...
  sss, 2008, c#, VB, .NET, ProperCase, Script Component
  2/17/2010  by MikeDavis  Comments: 0
• Using Stored Procedures in SSIS
  When you are creating an SSIS package with a data flow, it is a Best Practice to use a stored procedure for the queries. This makes it easy to adjust the queries without having to redeploy the packages. Here is stored procedure I am using. It is pulling from the Adventureworks2008 database which is free on codeplex. USE [AdventureWorks2008]GO/****** Object:  StoredProcedure [dbo].[Address]    Script Date: 02/16/2010 10:14:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [db...
  
  2/16/2010  by MikeDavis  Comments: 0
• SSAS 2008 Dimension Source Table Wrong
  I was working in SSAS 2008 the other day and I noticed the source table shows up wrong in my cube when I change the key column for a dimension. This does not break the dimension nor the cube, it is simply a strange bug. It does not happen all the time and seems to not cause any issues.   Here I am in the dimension named Store and you can see the attributes for that dimension below. I click on the state name to select it.    In the properties window I click the ellipses next to the key columns pr...
  SSAS 2008 Dimension Source Table
  2/11/2010  by MikeDavis  Comments: 0
• SSIS expression First day of next year
  If you just want January 1 of the next year here is the easiest way to do it. If you want a different year just change the "+1" to a different number. For example: if you want 2 years ago change it to "-2". This number could also be a variable to make the expression more dynamic.   (DT_DATE) ( (DT_WSTR,4)( Year(GetDate())+1) + "-01-01")   If you don't want the time then use.   (DT_DBDATE) ( (DT_WSTR,4)( Year(GetDate())+1) + "-01-01")
  SSIS expression First day of next year
  2/3/2010  by MikeDavis  Comments: 0
• Iron Chef Data Warehouse Edition SQL Saturday SSAS
  For those of you who did not make it to SQL Saturday in Tampa you missed the best presentation ever. It was an Iron Chef like competition between Brian Knight and Adam Jorgensen . They went head to head in an attempt to build a data warehouse from a university database. Adam actual pulled out power pivot and showed the power it contained. Both men developed a fully functioning cube with partitions and aggregations in the allotted time. Patrick Leblanc was the MC, he did a great job of covering t...
  Iron Chef Data Warehouse Edition SQL Saturday SSAS
  2/2/2010  by MikeDavis  Comments: 2