Beta Release - Please Report Issues
Who is online? 49 guests and 0 members
Member login | Become a member
Add a blog post
home » blogs » MikeDavis
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...
Read More
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.
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 tw...
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. Just remember the colors have to be valid colors. You could also use an expression with an if stateme...
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...
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...
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...
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 p...
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")
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...
It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.
enter your email address: