Moving Day

I have started moving the technical posts to pages to better organize the site. Please look in the drop down menus if you are looking for the Integration Services framework. Other topics, such as wait analysis, will be posted as time allows, so please be patient. Feel free to email if you have other topics or solutions that I could post. If you like what you see, drop a comment!

SSIS Framework – Day 2

Today, we will build a simple executable that will open the test package. For testing purposes, as well as for many scheduling utilities, we will be building a command line executable. The executable will  accept the package path  as an input parameter. Other features will be added in later days, but today we are starting small.

The first step is to create a console application in Visual Studio named SSISRunner.

New_project_screen
Reference

Visual Studio does not automatically create references to the required DLL’s for this solution. You will need to manually add a reference to Microsoft.SQLServer.DTSRuntimeWrap which was located in C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\ Microsoft.SQLServer.ManagedDTS.dll on my machine. The easiest way to ensure that all required components are available on your machine is to install the version of SQL server that you wish to target and include the SDK.

Once referenced, you can use it your program:
using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Design;

Code

Now, the easy part. Since the package is saved as file, opening the package is a simple call to Application.LoadPackage. Your code should look similar to this:

namespace SSISRunner

{

class Program

{

static void Main(string[] args)

{

string PackagePath = “”

Application app = new Application();

Package pkg;

//Get the path from the command line arguments

try

{

PackagePath = args[0].ToString();

}

catch (Exception EX)

{

Console.WriteLine(“USAGE:   SSISRunner <PackagePath>”);

}

//Open the package

if (PackagePath != “”)

{

pkg = app.LoadPackage(PackagePath, null);

}

}

}

}

Build

Build the application by pressing F6. This will create the executable in the C:\DOC\SSISRunner\SSISRunner\bin\Debug directory. This portion of the process can be the most confusing part. I am not an expert on Visual Studio or .Net. Assistance from professionals more knowledgeable than myself (of which there are many) may be required to get the application to compile for the first time. Now the good news: once the application compiles and is able to execute without errors, the rest will be easy.

SSIS Framework – Day 1

This series of articles relies on a basic understanding of SSIS. The approach is currently used by a large corporation with over 1000 SSIS packages scheduled by a centralized service, not SQL Agent. To date, more than 1,000,000 executions have been recorded in the history. Data is normalized in a manner that has enabled the logs for all executions to be stored on less than 15 GB of disk space. To date, none of the executions have been purged from the system allowing users to view timings of jobs executed in years prior.

Test Package

For testing purposes, I have created a package named “test” stored on my desktop, not within SQL. Yes, the name should be far more descriptive for production, but for today, our focus will be on some of the internals of SSIS. The package has one connection and one task.

test_package_control_flowtest_package_task_properties

 

 

What’s in the box?

Microsoft completed a massive upgrade from DTS to SSIS with the 2005 release. SSIS now uses XML to store all package properties. You can open, and with great care, modify the package in a XML or text editor. I would not recommend this for a production fix, but I have used the approach in the past to perform a mass update of all table names. In the case my test package, you can find the following:

SQLTask:SqlStmtSourceType=”DirectInput” SQLTask:SqlStatementSource=”USP_TEST_PROCEDURE”