BI Developer Network Blogs

Blogs: Most Recent postings

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

• SQL REPLACE for editing Configuration Tables
  Package Configurations in SSIS are key to making your packaged portable across the entire development process.  The problem you being to face with configuration files is the quantity that you collect.  Recently I was working on a project where we had pacakge level configurations as well as a configuration for each connection.  In order to to updates on this would have taken a very large amount of time, especially since I had to make configuration files for dev and production.  I opted to go with...
  TSQL
  11 hours, 36 minutes ago  by BradSchacht  Comments: 0
• Calculate the Date of the Previous Sunday
  There are a number of ways that you can calculate dates using T-SQL.  A common practice is to find the first day of the week which can be accomplished easily by using the following function.select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)However, you will notice that this returns the date for Monday as SQL Server uses Monday for the start of the week. If your week starts on Sunday or you need to get the date for the previous Sunday you can simply modify the DATEADD part of the query from adding z...
  
  11 hours, 36 minutes ago  by BradSchacht  Comments: 0
• Back to Basics
  Today I figured I go back to basics with this blog since a majority of the visitors to this site are primarily DBA's and Database Guru's. This may turn into a series but for now we will discuss a bit of a feared power of sql server, and that is the fact that sql server in fact sits on top of the .net framework. This allows it to access its immense power and array of options that one just couldn’t do efficiently using traditional tools, in both the BI Stack and SQL’s T-SQL function library. This ...
  
  15 hours, 19 minutes ago  by Bevans  Comments: 1
• Using a CTE with a T-SQL Pivot Statement
  During a T-SQL class that I was recently teaching I was asked if a PIVOT could be performed using a Common Table Expression (CTE).  In other words, instead of using the method outlined in SQL Server Books Online that uses a derived table, replace the derived table with a CTE.  I have never attempted this approach, but I was confident that it could be done.  Using the AdventureWorks database I initially wrote a T-SQL PIVOT statement that uses a derived table, which is as follows: USE AdventureWor...
  
  3/9/2010  by PatrickLeBlanc  Comments: 0
• Execute a SSIS package from a remote server
  Hi ,   I have a package that i need to be able to execute form a remote server....   How can i do that?... i need to have an icon on the desktop so the user can click on it and the package be executed. This is a very simple package where i have s source file (text) that i strip bad data and create a .CSV file. After that i copy the source file to an archive folder. i changed the source and destination directory to \\\\server_name\\sourcefoulder\\file_name , am i doing the right thing?   Regards,...
  
  3/9/2010  by mroman98  Comments: 6
• Reflection of Cloud Camp -- Auckland
  I have attended a cloud camp that was held in Auckland on Feb 26th 2010.  Here are some points sent by Ben the Cloud camp organiser. Here's some post-CloudCamp reflections and some posts from different people about the event. I posted a roundup here  giving my thoughts on the event. Thanks to all our sponsors who made the event possible - Gen-i, enStratus, Microsoft, salesforce, vmware, Intergen, the University of Auckland and Zendesk. Thanks also to Anne Bilek for doing a great job of helping t...
  Cloud camp
  3/9/2010  by indupriya  Comments: 0
• Date Dimension Indexes why not?
  Probably the most no-brainer in a DataWarehouse scenario is to index the heck out of the Date dimension. The cost of an index involves a couple of things: 1. How many inserts are performed on the table? 2. Fragmentation that may occur. 3. How much gain can be achieved by selects against the tables. So first off date dimensions only need a row per day so very little inserts occur. Fragmentation can be maintained by disabling and rebuilding the index. Last but not least the date dimension is argua...
  date dimension index
  3/8/2010  by MikeMollenhour  Comments: 0
• Filtering by Function performance alternative
  Many times we are forced to use functions in a where clause to filter results. The issue is here that rarely can SQL take advantage of an index if a function is used. SQL can perform OK in most of these scenarios, but what if you have a query used over and over again and you need it to perform as fast as possible. For the sake of this Blog let’s assume we have a query that needs to filter rows by month. In our query we would typical have something like this “where month(salesdate) =3”.  So what ...
  performance functions where clause
  3/6/2010  by MikeMollenhour  Comments: 0
• Another error message that doesn’t make sense, or does it?
  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...
  
  3/5/2010  by timmurphy  Comments: 0
• 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