Centralized Data Collecting, Using SSIS

What is the best method to centralize data collection and/or history metadata related to SQL Server Instances?  This is a common question that a lot of companies/developers ask themselves.  A lot of solutions, especially pre SQL Server 2008,  require objects to exist on each participating instance, or require that you create a batch process that loops through a text file of server names.  SQL Server 2008 introduced a centralized storage mechanism to collect and manage performance data for 1 or more instances, aliased as Data Management Warehouse.  Here is a great link that briefly covers the new 2008 feature, http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/.  Today I will be focusing on a highly scalable SSIS solution.  SSIS offers a lot of flexibility,  scalability and has a lot of different security measures, that are not available to TSQL and command line programming.   For those unfamiliar with SSIS, SSIS (SQL Server Integration Services) is a tool used to transform and load data amongst a variety of sources including SQL Server, flat files, Oracle, AS400, MS Access etc.   In a recent blog post, I demonstrated how to setup a new SSIS package and how to dynamically change the destination file name.  This post has a lot of meat for beginners and may be worth a read,  http://jahaines.blogspot.com/2009/07/ssis-dynamically-naming-destination.html.  I will not get into the particulars of creating the SSIS package here, but will recommend that any persons not familiar with SSIS to read the above linked post.

Let’s get started by opening SSMS and browsing to the SQL Server instance you want to use as your central data store.  I chose to create a new database called “SQLMngt_DW”.  The ultimate plan for this database is to store job history metadata for all SQL Server Instances.  Feel free to create your database where you see fit.

USE [master]

GO

CREATE DATABASE [SQLMngt_DW] 

GO

Now lets create a table to store server names.

USE [SQLMngt_DW]

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'Servers')

BEGIN

    DROP TABLE dbo.[Servers];

END

GO

--Create a Servers table to house all your instances

CREATE TABLE dbo.Servers(

InstanceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

InstanceName VARCHAR(50)

);

GO

Now we can insert some ServerNames into our dbo.Servers table.

USE [SQLMngt_DW]

GO

INSERT INTO dbo.Servers VALUES ('Instance1\dev');

INSERT INTO dbo.Servers VALUES ('Instance2\dev');

**Note: Make sure to insert some instance names into the dbo.Servers table; otherwise, your package will timeout when trying to dynamically change the connection string. Also, make sure you enter valid instance names.  The names above are dummy server names, you should alter the inserts for you environment.

We need to create on last table, called JobHistory.  This table will store job history data for all servers listed in the servers table.

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'JobHistory')

BEGIN

    DROP TABLE dbo.[JobHistory];

END

GO

--Create a job history table

CREATE TABLE dbo.JobHistory(

JobHistoryId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

ServerName VARCHAR(50),

JobId UNIQUEIDENTIFIER,

JobName VARCHAR(50),

RunDate DATETIME,

JobStatus VARCHAR(11),

Duration VARCHAR(10)

)

Now that we have our database and tables, we can start creating our package.  Create a new SSIS package and create two variables. One variable will need to be of object type and the other of string, as shown below.  The value for the string variable, which I named “connectionString” should have a value equal to the server name where you placed your centralized database.

Next drag the Execute SQL Task component to the canvas.  Your canvas should look like below.

 

Open the Execute SQL Task and configure the general tab, as shown below. Make sure to create a connection to the instance where you created your database and JobHistory table. I named my connection SQLMngt_DW.

The code for the SQL Statement is below.

select InstanceName from dbo.Servers

Now, we need to configure the result set tab.  Make the appropriate configurations, as shown below.

Our SQL Task is fully configured.  We can proceed in creating the ForEach task.  Drag the ForEachLoop Container to the canvas.  Make sure to create a precedence constraint between the SQL Task and the loop.  Essentially, all you need to do is drag the green arrow from the SQL task to the ForEachLoop Container. Once the loop container is in place, you have to drag a data flow task into the container. Your canvas should look like the screenshot below.

Now we have to configure both controls.  Let’s start with the loop.  Double-click the foreachloop container to configure the properties.  I will start with the collection tab. 

Next configure the variable mappings tab, as shown below.

Now, we have to configure the Data Flow Task.  Create both a source and destination OLE DB task, within the Data Flow Task, and create a precedence constraint between the two.  Once the source and destination have been created, you should create a new OLEDB connection.  This connection string needs to point to any valid Instance. Note: It does not matter which instance you choose.  I have chosen to point my new connection to the same server as the SQLMngt_DW.  I named my new connection dyn_source (dynamic source).  Your canvas should look like below.

Now open the OLE DB Source, in the data flow task and configure it, as shown below.

Note: The code for the SQL Command is below the screenshot.

SQL Command Code:

SELECT 

    CONVERT(VARCHAR(50),@@SERVERNAME) AS ServerName,

    j.[job_id] AS JobId,

    CONVERT(VARCHAR(50),j.NAME) AS JobName,

    CONVERT(DATETIME,CONVERT(VARCHAR(8),jh.[run_date]) 

    + SPACE(1) 

    + STUFF(STUFF(RIGHT(REPLICATE('0',6) 

    + CONVERT(VARCHAR(6),run_time),6),3,0,':'),6,0,':')) AS rundate,

    Run_Status.descr AS jobstatus,

    STUFF(STUFF(RIGHT(REPLICATE('0',6) 

    + CONVERT(VARCHAR(6),Run_Duration),6),3,0,':'),6,0,':') AS duration

FROM msdb..[sysjobs] j

INNER JOIN msdb..[sysjobschedules] jsch

    ON j.[job_id] = jsch.[job_id]

INNER JOIN msdb..[sysschedules] sch

    ON jsch.[schedule_id] = sch.[schedule_id]

INNER JOIN msdb..[sysjobhistory] jh

    ON jh.[job_id] = j.[job_id]

INNER JOIN(

    SELECT 0 AS run_status ,'Failed' AS descr UNION ALL

    SELECT 1,'Succeeded' UNION ALL

    SELECT 2,'Retry' UNION ALL

    SELECT 3,'Canceled' UNION ALL

    SELECT 4,'In progress'

) AS Run_Status

    ON jh.[run_status] = Run_Status.[run_status]

WHERE

    j.NAME NOT LIKE 'Tivoli%' --tivoli backups

    AND j.[enabled] = 1

    AND sch.[enabled] = 1

    AND jh.[step_name] = '(Job outcome)'

    AND CONVERT(DATETIME,CONVERT(VARCHAR(8),jh.[run_date]) 

        + SPACE(1) 

        + STUFF(STUFF(RIGHT(REPLICATE('0',6) 

        + CONVERT(VARCHAR(6),run_time),6),3,0,':'),6,0,':')) 

        BETWEEN DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

            AND DATEADD(DAY,DATEDIFF(DAY,0,GETDATE())+1,0)

Make sure to click the columns tab so SSIS can automatically set your columns for you.

Now let’s configure the destination.  The destination is actually quite simple.  We have to open the properties and choose our  SQLMngt_DW source and the JobHistory table, as shown below.  Make sure to select the mappings tab to verify all the columns are aligning properly.  You may see the JobHistoryId column is set to ignore, which is fine because this is an identity column and we do not want to insert directly into it.

Now we get to do the fun part :-), which is setting up the dynamic connection string via the connectionstring variable.  Right-click the dyn_source connection string and click properties.  Once the properties display, you can click the ellipses next to expressions and choose connectionstring.  Click the ellipses in the expression textbox to open the expression editor and paste the following expression:

"Data Source=" + @[User::ConnectionString] + ";Initial Catalog=msdb

;Provider=SQLNCLI10.1;Integrated Security=SSPI"

Note: I am using Integrated security. If your connection string is use SQL Authentication, you will need to supply username and password in the connection string.

Believe it or not, that is it!!!!   Click on the play sign at the top of the SSIS application to run the package.  We should see all tasks light up green, unless something is not configured correctly, in which case you will see red. 

Our last step is to validate our package did what it is supposed to do.  We can validate this by querying our job table.

There you have it.  You now have a very flexible and scalable method to collect data from one or more sources.  The SSIS method really beats having to manage a command line solution or having to manage/deploy stored procedures to each server.  Configuring the SSIS package is not that tedious and can be done fairly quickly.  It actually takes a lot longer to describe how to do this, than actually do it.  Once you get use to using SSIS, this process should only take you 10-15 minutes to setup and configure.   I hope that you have learned something new and I know this will help some of you out there.  I know this post may seem overwhelming for those of you have not really had the opportunity to dive into SSIS yet, so I have provided a link, where you can download the project.  Please be aware that I am using SQL 2008 and BIDS 2008, so my package will not work with BIDS 2005, as the package structure changed in BIDS 2008.

Download the project files: http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/SSIS%7C_Data%7C_Collection/SSIS%7C_DataCollection.zip

Happy coding!!

SSIS – Dynamically Naming Destination Output Files

When I am not wearing my DBA hat, I put on my BI (Business Intelligence) hat.  BIDs (Business Intelligence Development Studio) is a very powerful and scalable business intelligence software package that was introduced in SQL Server 2005.  Within BIDs, I will focusing on SSIS (SQL Server Integration Services) projects.  SSIS projects are designed to extract, transform, and load data, which is more commonly known as ETL.  In this post, I will be focusing on extracting data and loading it to a destination, there is no need for me to transform the data.  I will start by creating a new SSIS package.  You can launch BIDs by browsing to Start –> Programs –> SQL Server 2005/2008 –> SQL Server Business Intelligence Studio. Once BIDs has launched, you should click New Project and select Integration Services Project, as shown below.

We will name our project DynamicDestinationFileName.  Once the project is created, we will switch over to SSMS and create a sample table, in tempdb.  Log into your instance of SQL Server and execute the code below.

SET NOCOUNT ON;

GO

USE [tempdb]

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'MyTable')

BEGIN

    DROP TABLE dbo.MyTable;

END

GO

CREATE TABLE dbo.MyTable(

Id INT IDENTITY(1,1) PRIMARY KEY,

Col CHAR(1)

);

GO

INSERT INTO dbo.MyTable VALUES ('A');

INSERT INTO dbo.MyTable VALUES ('B');

INSERT INTO dbo.MyTable VALUES ('C');

INSERT INTO dbo.MyTable VALUES ('D');

INSERT INTO dbo.MyTable VALUES ('E');

GO

We have just created our SSIS data source, which means this is the table that will feed our destination flat file.  Switch back to BIDs and drag a “Data Flow” task to the designer, from the toolbox. You screen should look like below.

Now we can double-click the data flow task to configure it, or you can click the data flow task tab above the canvas.  We will need to drag a OLE DB data source and a flat file destination, from the toolbox.  Your canvas should look like below.

Select the OLE DB Source and drag the green arrow to the flat file destination.  Now, let’s configure the source.  Double-click the source and click New.

Click new again and then type in the database server where you just ran the scripts to create the table.  In the database dropdown select tempdb.  Your connection should look like below.  You can test your connection by clicking the test connection button.  After testing the connection, click ok to apply the changes and then ok again. 

Now choose MyTable from the table dropdown. 

Once you select the table, click the columns tab to assign columns to the data source.  Click Ok and were down with the data source!!!! Now let’s move onto the destination.

Double-click the flat file destination and create a new connection.  When you click “New” to create the destination connection a window will popup and ask for the flat file destination format. 

The options include delimited, ragged right, fixed-width etc.  The differences between these types is not relevant to the task at hand, and will not be covered here.  BOL has a lot of information about the differences between output types.  Okay, lets move on by selecting delimited and ok.

Now we will need to define the properties of the destination file. Click the browse button to choose where the destination file will be created.  I will be creating my file to the C:\MyTable.txt.  you may be screaming, HUH!!!!, YOU SAID THE DESTINATION NAME WOULD BE DYNAMIC!!!!!!  Do not fret it will be and I will show you how to transform the static destination to a dynamic one, in a few minutes.

Okay now, click the columns tab on the left to make sure the columns look correct.  You should see the columns Id and  Col.  If everything looks good, click ok.  Note: there should not be any data because the flat file is empty at present, but the columns should still be correct.

Now we should be in the destination editor window.  Click the mappings tab, as shown below and make sure everything aligned properly. If so, click ok.

I hope you are still with me :-).  Finally we can get to the meat of the post.  Select the Flat file connection manager in the connection managers pane –> right-click and choose properties and click the ellipses next to “expressions”, as shown below.

Choose connection string from the dropdown and then click the ellipses in the expression box.  You will have to build the expression to create the destination connection string.  The expression editor uses VB coding, so that may be a benefit for some of you out there.  I have provided the code below to build the expression.  Click the evaluate button to see what the connection string will evaluate too. Note: I had to use double backslash because it is required because the backslash acts as a escape character, in the expression editor.

Note: I like to use variables to store file paths because it makes the code cleaner and is easier to modify the package, as you only need to update the variable in once, whereas, you may have to update lots of expressions, if they are hardcoded.

"C:\\MyTable_" +

Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +

Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + 

(DT_STR,4,1252) DatePart("yyyy",getdate()) + ".txt"

Here is what the expression looks like and should evaluate too.

Now lets, run our package and test it out. Click the green play symbol to run the package.  When you run the package all the tasks should turn green, which indicates they succeeded.  If yours turned red, something went wrong. 

Now we just have to make sure our file is where we put it and the name contains today’s date in MMDDYYYY format. 

We are good, the package did everything we wanted it too.  So there you have it…. a method to create a destination file, with a dynamic name.  It is really quite easy to do, the tedious and mundane part is creating and setting up the package itself.  I hope you learned something new and can use this now or somewhere down the road. 

Stay tuned, I will be posting a lot more BI solutions, tips,  and tricks.