Category Archives: Uncategorized

SSIS Token Function

Microsoft quietly added two functions to their SSIS expression language: Token and TokenCount. I think that these functions will find their way into any serious SSIS developer’s tool belt. Here I show how you can use this to solve a … Continue reading

Posted in Uncategorized | Comments Off on SSIS Token Function

Cost of using SSIS Variables

When I think of a variable in programming, I thing of a construct that is not much more costly to use than the equivalent constant.  Thus, if I had the following loop in C#, I could replace the variable “splitString” … Continue reading

Posted in Uncategorized | Comments Off on Cost of using SSIS Variables

SSIS 2012 Variables window versus Locals window in debug

I have wasted most of this afternoon with a dumb problem.  I was testing some regular expressions, but for some reason the script task was not letting me break, so I create an SSIS variable (SSIS 2012) and made it read-write … Continue reading

Posted in Uncategorized | Comments Off on SSIS 2012 Variables window versus Locals window in debug

Deny select on table Grant select on columns

There is a little warning in books-on-line about the interaction of Deny and Grant (http://msdn.microsoft.com/en-us/library/ms173724.aspx).  “A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.” However, I found … Continue reading

Posted in Uncategorized | Leave a comment

SSIS Location of currently running package

I have been wanting figure this out for a while.  Here is the problem.  You run your package in a package that is deployed using the SQL 2012 Project deployment model.  You want to get the folder and project that … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

Shredding multiline column using xml

I had an ETL project to copydata from one SQL Server to another. The conversion rules, the data constraints and other metadata were in an Excel spreadsheet. Many of the cells consisted of multiple rows of data. But how to … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

SQL 2012 – Generate Calendar with Cross joins

“cross join” is a difficult to explain to trainees.  The mechanics aren’t very complicated.  However, the question is where you would ever use them? One example that I came up with is generating a calendar.  Let’s say I want to … Continue reading

Posted in Uncategorized | Tagged , , , , , | Leave a comment

SSIS Template, changing GUIDs

There has been a basic method from SSIS 2005 for creating an SSIS package template.  You create package, then put that package into a magical folder (under the Common7 folder of the appropriate version of Visual Studio), and voila you … Continue reading

Posted in Uncategorized | Tagged , , | Leave a comment

SSAS 2012 – You do not have permission to run ‘SP_TRACE_CREATE’.; 42000

I am preparing to teach a class on SSAS.  I am trying to run the demonstration and labs  on my local machine.  My machine has several differences from the lab environments provided by Microsoft.  I don’t have an adventureworks domain … Continue reading

Posted in Uncategorized | Leave a comment