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” with a constant and the performance impact would be minimal. int splitLength = 3; string […]
Cost of using SSIS Variables Read More »
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 and assigned the value. I then put a break on the PostExecute event. I viewed
SSIS 2012 Variables window versus Locals window in debug Read More »
Here is an annoying problem. You are dealing with dozens of SQL Server databases and you are looking for a particular table in one of the databases. How can you find the table? Do you click through all of the databases to find the one that houses that table? Here is a simple query to find a
Finding table in database Read More »
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 a little caveat to this point. The order in which you issue the GRANT and
Deny select on table Grant select on columns Read More »
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 the current package is running under. How do you do this? First you need a
SSIS Location of currently running package Read More »
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 extract the rows? I was able to shred these cells into SQL rows by using
Shredding multiline column using xml Read More »
“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 generate a list of dates in a set of years. For example, I want a
SQL 2012 – Generate Calendar with Cross joins Read More »
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 have a package template (see http://www.mssqltips.com/sqlservertip/2841/creating-ssis-package-templates-for-reusability for the full process). A major problem with this process has been
SSIS Template, changing GUIDs Read More »
One of the things that I like about providing training is the kind of questions that come up. Many times they are issues that I have thought about but been just plain too lazy to examine. This week one of my students asked if it were possible to lookup at record in another table and
SSIS: Using Lookup to not only find records, but add them too Read More »
I have been meaning to write my thoughts about SQL Server for a while. I hope my thoughts will help others with their SSIS and SQL questions as well as to help me to learn from writing as well as from the input of others. I am preparing for a very exciting week of SQL