One of the things that messes up processing delimited files is when the delimiter character is actually part of the data rather than acting as a delimiter. So look at the following: The second line has 4 commas but the first has only 3 commas. The second comma is actually part of the data: One […]
Selective text qualifiers in SSIS Read More »
When using dynamic SQL there is always the possibility that someone will inject SQL that does harm. They can put queries that can drop objects or can create sysadmin logins. Running the scripts as low permission users can reduce the potential harm. But that takes time to research and is easy to cut. You can
Limiting permissions with “Execute as” when using dynamic SQL Read More »
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 very common problem, interpreting dates. When importing data, frequently one column of data will need
SSIS Token Function Read More »
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 »