Executing an SSIS package from within a stored procedure is not an uncommon request. People sometimes scratch their head when it comes to figuring out if that package ran successfully or not though. There may be a process that runs a stored procedure and kicks off a certain SSIS package depending on the situation. Maybe there are parameters that were used in the stored procedure that need to...
August 5, 2010
Capture SSIS Package Execution Status
05August
July 28, 2010
Truncating A Replicated Table
28July
On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server. Much of the ETL had places where tables were truncated and reloaded. Normally this is not an issue at all, simply TRUNCATE TABLE and away you go. This does however cause a problem when you have a replicated database. According to the Microsoft documentation, which...
July 16, 2010
CREATE SCHEMA Syntax Error
16July
Recently while creating a script to migrate the new holding tables over to the stage and production machines I ran into a little issue. The holding table we are using in this case houses several different sets of data for our migration project. Tables that are used only for going from the old system to the new (which will be going away in a few months) and tables that will be used in the future...
June 23, 2010
Comma Delimited List with COALESCE
23June
There may be a day when you need to create your own comma delimited list from data contained in a SQL table. Sure you could build a cursor and go through each row adding a comma after each record. The good news is that there is an alternative. The better news is that this method is easier, faster and takes MUCH less code. COALESCE in SQL has many uses and building a list is one of them...
April 28, 2010
Server ‘SERVERNAME’ is not configured for RPC
28April
I recently was doing some development on my local machine and needed to pull data from a linked server. I set up the linked server and everything was working great. Executing queries to pull the data to my machine was fine, but a problem arose when I needed to execute a stored procedure from the other server. First of all, to call a stored procedure from a linked server you need to use a four...
April 6, 2010
Open File – Security Warning in SSIS
06April
When running packages that contain executable and batch files you may sometimes come across an issue where you need to confirm that you want to open the file. This can cause an issue if you run the package unattended. Obviously, this is a security precaution that is built into the many of the newer versions of windows.You have to authenticate sessions, run as administrator, and confirm that you...
March 29, 2010
Removing The Date From The End of a String
29March
Occasionally there may be the need to remove the date from the end of a string. This can be accomplished by using PATINDEX along with SUBSTRING. We will use PATINDEX to find where the date begins and then SUBSTRING to remove the date from the end of the file. We will use ClientName03302010.csv and we want to get ClientName.csv from that to process. PATINDEX requires an expression to search...
March 24, 2010
Joining Tables That Contain NULL Values
24March
As you can see we have two tables, with the same values. Joining on these tables is no problem at all. Simple, right? Well, take a look at what happens when we try to join on the column containing the NULL values. The results are the following: As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result...
March 10, 2010
Calculate the Date of Previous Sunday
10March
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...
February 17, 2010
Use SQL from the Command Line
17February
More often than not an execute SQL task in SSIS will do the trick. If not, then the data flow will accomplish whatever objective is set out. Occasionally those can become cumbersome in a package to accomplish something simple. In those cases using SQL from the command line could be the perfect fit. SQLCMD will allow SQL commands to be run from the command line in has the ability to define...
