SSRS - Should I Use Embedded TSQL Or A Stored Procedure?

SSRS is becoming a highly scalable and performant reporting solution, for most environments.  SSRS is becoming more and more popular because of its price tag.  It is really tough to compete with free.  The only cost  consideration that needs to be made is SQL licensing.  With the uproar with BI and Share Point SSRS is becoming the premier reporting platform.  As database professionals, we need to consider the performance consequences of all the code that executes on our production/reporting databases.  SSRS is a great reporting tool but if left unchecked can be quite problematic.  This post will strictly focus on the eternal question…. should I use a stored procedure, or should I use embedded SQL. I will be addressing this question from the DBA perspective, which is often neglected. 

I will be very frank and say that I really do not see any benefits to using embedded TSQL from the DBA perspective.  Embedded TSQL has a lot of cons that should deter any database professional from using it.  So what are some of the problems in using embedded TSQL?  Let’s name a few of the cons.

The Cons Of Embedded TSQL:

• Harder to manage security
• Report may break when schema changes
• Difficult to make changes to embedded TSQL
• Causes procedure cache to bloat

Note: I did not list the pros to using stored procedures, but the list is the inverse of the Embedded TSQL list. 

As you can see, there are a lot of problems in choosing to use embedded TSQL.  The first con to using embedded TSQL is security.  It is extremely difficult to manage security without the use of stored procedures.  When code logic is encapsulated in stored procedure, the DBA can easily apply permission to the stored procedure, without elevating permissions to the underlying objects.  If embedded TSQL is used, the person executing the report must have underlying permissions to all objects referenced in the embedded TSQL, which makes maintaining embedded TSQL complicated because you really have no idea what code is actually being executed against your database.  To get an idea of what permissions are needed to execute a report, you have to open the report or run a trace to get the TSQL.

Embedding TSQL in a SSRS report also can become a problem when the underlying database schema changes.  The SSRS report has no dependencies on the database schema and any change can break a report.  In this scenario, there may be additional downtime just to figure out and fix the problem.  In most cases, the DBA has no idea that a schema change broke the report. Typically the problem does not surface until customers start complaining, which leads to the problem of changing the embedded TSQL.  This is not to say that when stored procedures are used reports will not break, but SSMS offers better dependency checks to determine what objects are dependant, which decreases the likelihood of an report outage. 

One of the biggest problems with embedded TSQL is modifying the TSQL code.  To modify the embedded TSQL the developer has to download the report RDL and then make the change.  Once the change has been made, the developer has to redeploy the report.  These steps require a lot of time to implement and additional downtime is incurred.  If a stored procedure is used, the only downtime incurred is the time taken to modify the stored procedure.  Another benefit of a stored procedure is a developer or DBA can more easily test the report within the confines of SSMS, instead of having to use BIDS.

The absolute worse aspect of using embedded TSQL is it can bloat the procedure cache, which can severely degrade server performance. SSRS tries and does a good job at parameterizing most TSQL, but there are certain aspects of SSRS that cause the procedure cache to bloat.  This is where I want to focus most of my attention because this is often the most overlooked aspect of embedded TSQL.  There are two scenarios that I am currently aware of that can directly cause the procedure cache to bloat.  The first scenario occurs when a multi-value parameter is used in conjunction with the IN clause.  Multi-value parameters are treated a differently than standard parameters, in SSRS.  When a multi-value parameter is used with the IN clause the SSRS engine submits the query to SQL Server using literal values in the IN clause.  When literal values are used in the IN clause, the query is not considered parameterized, so the optimizer has to create a new query plan, unless an exact binary match already exists. Let’s have a look to see this example in action.

First let’s create the sample table and populate the table with data.

SET NOCOUNT ON

GO

USE [tempdb]

GO

IF object_id('tempdb.dbo.SSRS_Cache_Bloat') IS NOT NULL

BEGIN

    DROP TABLE dbo.SSRS_Cache_Bloat;

END

GO

CREATE TABLE SSRS_Cache_Bloat(

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

ColA VARCHAR(10),

ColB BIT

);

INSERT INTO dbo.SSRS_Cache_Bloat VALUES ('Adam',0);

INSERT INTO dbo.SSRS_Cache_Bloat VALUES ('Bob',1);

INSERT INTO dbo.SSRS_Cache_Bloat VALUES ('Chad',0);

INSERT INTO dbo.SSRS_Cache_Bloat VALUES ('Dave',1);

GO

IF object_id('tempdb.dbo.Lookup_Vals') IS NOT NULL

BEGIN

    DROP TABLE dbo.Lookup_Vals;

END

GO

CREATE TABLE dbo.Lookup_Vals(

ColA CHAR(4)

);

INSERT INTO dbo.Lookup_Vals VALUES ('Adam');

INSERT INTO dbo.Lookup_Vals VALUES ('Bob');

INSERT INTO dbo.Lookup_Vals VALUES ('Chad');

INSERT INTO dbo.Lookup_Vals VALUES ('Dave');

Next create a new SSRS report. Create two data sets and a parameter, as defined below.

DataSet1 is the main report dataset.

select Id, ColA, ColB from SSRS_Cache_Bloat where ColA in(@var)

DataSet2 is the parameter dataset.  You will need to make sure your parameter derives its values from this dataset.  

select colA from dbo.Lookup_Vals

Make sure the parameter is set to use multi-value parameters, as shown below.

Once you have all the data sets configured.  Preview the report in SSRS.  When selecting your parameter values make sure to select more than one value.  Here is a screenshot of my report.

As you can see the multi-value parameter returned a row for all three parameter values.  Let’s look at the query execution stats to see what SQL actually executed.

SELECT TOP 10

        qs.execution_count,

        qt.text,

        qt.dbid, dbname=db_name(qt.dbid),

        qt.objectid 

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY qs.[last_execution_time] DESC

You should see an entry similar to the screenshot below.

As you can see, SSRS submitted TSQL with literal values specified in the IN clause.  What do you think will happen, if we preview the report again with different parameter values?  If you guessed that we will get a completely new plan, you would be right.

Can you imagine what happens when you have hundreds or thousands of differing options and hundreds or thousands of users?  The plan cache will take a beating because so many plans with differing values will have to be stored.  When so many plans exist in the procedure cache, you have less memory to store data pages in cache.  Ultimately nothing good comes out of having a bloated procedure cache. Multi-value parameters are not the only cause of bloating the cache.  The next scenario that bloats the procedure cache is using hard coded values in a parameter list.

Using hard coded values in a parameter list seems like a harmless gesture, but the reality is the SSRS engine guesses at the size of the string which directly impacts whether an existing plan can be used.  In our SSRS report change the dataset that is used to query the Lookup table to use fixed values, as shown below.

Note: I added x to some of the values so that the length varies among strings.

Let’s preview the report, to see what happens. I used the values “Adam” for the first execution and “Bob” for the second execution.  You should see entries like below in your procedure cache.

The primary difference between the two execution plans is the size of the declared variable.  In the case of “Adam” the variable was declared as a nvarchar(4) and for “Bob” a nvarchar(3).  Because the size is different a new query plan was created. 

These are the couple of scenarios that I am currently aware of that cause the plan cache to behave in this manner.  I am sure there are other quirks that can cause this problem.  So the big question left on the table is…. how do I fix this problem?  The answer is to use stored procedures. 

I will start by fixing scenario two.  Create the following procedure in the database.

CREATE PROCEDURE usp_Fix_Scenario2(@var varchar(10))

AS 

BEGIN

    SELECT id,ColA,[colB]

    FROM dbo.SSRS_Cache_Bloat

    WHERE ColA IN(@var)

END

GO

You will see a single entry in the procedure cache, for both “Adam” and “Bob”.  As you can see the execution count is at two, which means the optimizer reused an existing plan.

Now let’s fix scenario one.

CREATE PROCEDURE usp_Fix_Scenario1(@var varchar(100))

AS 

BEGIN

    DECLARE @x XML

    SET @x = '<i>' + REPLACE(@var,',','</i><i>') + '</i>'

    SELECT id,ColA,[colB]

    FROM dbo.SSRS_Cache_Bloat

    WHERE ColA IN(

        SELECT x.i.value('.','varchar(10)')

        FROM @x.nodes('/i') x(i)

    )

END

GO

As you can obviously see using stored procedures is by far a best practice.  Stored procedures allow the greatest security, flexibility, manageability, and performance. I really cannot see a reason to use embedded TSQL at all and hopefully at this point you feel the same way.  All-in-all, we learned a valuable lesson in this post.  You cannot always trust that easier is better even if Microsoft says it is okay.  SSRS is tool written with the developer in mind and the DBA perspective is neglected.  If DBAs knew what SSRS is really doing behind the scenes, embedded TSQL would be outlawed. We as DBAs have to know and expose potential performance problems for all applications including Microsoft applications.  I hope that my exposing these flaws within SSRS, will help you and your environment adhere to better SSRS practices.

Until next time, happy coding.

Exporting Binary Files To The File System

In my last post I demonstrated how to use SSIS to load binary files into a SQL Server 2005 VARBINARY(MAX) column, http://jahaines.blogspot.com/2009/10/ssis-importing-binary-files-into.html.  This post will focus on recreating the binary documents on the file system.  I will be using a combination of TSQL and the BCP utility to perform the export, http://msdn.microsoft.com/en-us/library/ms162802.aspx.  I will be using the same table and data from the last post.  I will start by creating the TSQL to dynamically create a BCP command.

Below is the stored procedure I will use to export the data.  You will see that I have decided to use a cursor to process all documents.  The procedure also accepts a DocID, which limits the export to a single document.  A cursor is fine here because we are limited to executing a single BCP command; however, you could create an SSIS package that executes the stored procedure across multiple streams, if you need parallel processing.

CREATE PROCEDURE usp_ExportBinaryFiles(

    @DocID INT = NULL,

    @OutputFilePath VARCHAR(500) = 'C:\'

)

AS 

BEGIN

DECLARE @sql VARCHAR(8000)

IF @DocID IS NULL --Open Cursor to export all images

BEGIN

    DECLARE curExportBinaryDocs CURSOR FAST_FORWARD FOR

    SELECT 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 

        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 

        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'

    FROM dbo.Documents

    OPEN curExportBinaryDocs

    FETCH NEXT FROM curExportBinaryDocs INTO @sql

    WHILE @@FETCH_STATUS = 0

        BEGIN

            --PRINT @sql

            EXEC xp_cmdshell @sql,NO_OUTPUT

            FETCH NEXT FROM curExportBinaryDocs INTO @sql

        END

    CLOSE curExportBinaryDocs

    DEALLOCATE curExportBinaryDocs

END

ELSE --Export a single image

BEGIN

    SELECT @sql = 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 

        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 

        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'

    FROM dbo.Documents

    WHERE DocID = @DocID

    --PRINT @sql

    EXEC xp_cmdshell @sql,NO_OUTPUT

END

END

GO

The above stored procedure dynamically builds a BCP command.  Currently the stored procedure has two main parameters, DocID and OutputFilePath.  DocID is used when you want to export a single row, if no value is supplied the stored procedure will export all documents.  The outputFilePath is the directory where the files will be exported.  You will note that I have hard coded my server name and format file path.  You can add additional parameters if you need these attributes to be dynamic.  The next item on my list is to actually create the format file.  Create a .fmt file somewhere on your file system.   I put mine in the same folder as my other documents.  Copy the code below into the format file.

9.0   

1   

1       SQLBINARY     0       0       ""   1     Doc                                       ""  

You may be asking yourself, why the format file looks skimpy, or is lacking content.  The format file is lacking content because it only has what we need.  All we need in the format file is the VARBINARY(MAX) column and its data type. You can find more info on format files here, http://msdn.microsoft.com/en-us/library/ms191516.aspx.

Once the format file is in place, we can execute our stored procedure to process all documents.

EXEC dbo.usp_ExportBinaryFiles @OutputFilePath = 'C:\Documents\BCP_Out\'

That’s it!  It is that easy to output VARBINARY data onto the file system.  SQL Server 2005 has a slue of tools that make working with binary data very simplistic.  I hope that you have learned something new. Please stay tuned, as I plan to focus more on TSQL concepts and performance considerations.

Until next time happy coding.

SSIS – Importing Binary Files Into A VARBINARY(MAX) Column

Have you every had the need to import images, Word documents, Excel documents, or any other type of file into a SQL Server table?  If yes, you are in luck.  SQL Server 2005 gives us a powerful and scalable method to import binary files or documents, into a relational table.  I will be using the term binary file and document interchangeably throughout this post to describe a file on the file system.  I will be utilizing SSIS and the VARBINAARY(MAX) data type to import the document. Let us start by creating the sample DDL.  In this example, I will need a staging table and a table to house our binary data. 

USE [tempdb]

GO

IF OBJECT_ID('tempdb.dbo.[Doc_Stage]') IS NOT NULL

BEGIN

    DROP TABLE [dbo].[Doc_Stage];

END

GO

CREATE TABLE [dbo].[Doc_Stage](

DocId INT IDENTITY(1,1) PRIMARY KEY,

DocName VARCHAR(50) NOT NULL,

DocPath VARCHAR(1000) NOT NULL,

DocType VARCHAR(4) NOT NULL

);

GO

IF OBJECT_ID('tempdb.dbo.[Documents]') IS NOT NULL

BEGIN

    DROP TABLE [dbo].[Documents];

END

GO

CREATE TABLE [dbo].[Documents](

DocId INT IDENTITY(1,1) PRIMARY KEY,

DocName VARCHAR(50) NOT NULL,

Doc VARBINARY(MAX) NULL,

DocType VARCHAR(4)

);

GO

Next, I need to create the SSIS package.  The first step is to add an Execute SQL Task to the designer canvas.  Configure the Execute SQL Task, as shown below.

Note: You will need to create the source connection to the database where you created the DDL

 

The SQL Statement that I used is below.

TRUNCATE TABLE [dbo].[Doc_Stage] ;

Next I will create a variable called FilePath that is of the string data type.  Now I am ready to add a ForEach Loop Container to the canvas.  Add the container to the canvas and configure it as shown below.

Note: I am grabbing all file types. If you only want a specific type, change the File to include the extension that you want.  E.g. *.jpg

Next, I will have to create another Execute SQL Task, but this time, I have to drag the task into the ForEach Loop Container.  Configure the task as shown below.

The SQL Statement is presented below:

INSERT INTO [dbo].[Doc_Stage](DocName,DocPath,DocType) VALUES ('DocName','DocPath','jpg');

Now for the tricky part.  In this step, I have to build an expression to dynamically build an insert statement.  The insert will capture the document name, path, and type.  Click the expressions tab and create an expression, on the property SQLStatementSource (near the bottom).  Below is the code for the expression.

"INSERT INTO [dbo].[Doc_Stage](DocName,DocPath,DocType) VALUES ('" +

REPLACE(RIGHT(@[User::FilePath],FINDSTRING(Reverse(@[User::FilePath] ) ,"\\", 1)-1),RIGHT(@[User::FilePath],4),"")

+ "','" + @[User::FilePath] 

+ "','" + RIGHT( @[User::FilePath] ,FINDSTRING( REVERSE(@[User::FilePath] ),".",1)-1)

+ "');"

 

With that out of the way, we can press on.  The last task I will need is a Data Flow Task.  Drag the data flow task to the canvas.  Here is what my canvas looks like at present:

Open the Data Flow task and drag an OLE DB Source to the canvas and configure it as shown below.

 

Make sure to click the Columns tab to set the column mappings.  Next, I will drag a Import Column Transformation to the canvas and configure it as shown below.

Note: Make sure to take note of the output column’s LineageID.  You will need to take this ID and add it to the Input Column’s FileDataColumnID.

We are almost there!!! The last step is to add the OLE DB Destination.  I will add the OLE DB Destination and configure it as shown below. Do not forget to click the Mappings tab to map the columns.

That’s it!!!! Click the debug button and all of the components should light up green. I have successfully implemented a document library solution that allows the insertion of any document type into SQL Server 2005.  Stay tuned because I will show you how to export the images to the file system using BCP and TSQL.

Until next time happy coding.

SSIS – Performing An UPSERT

For those of you who may be wondering what the heck an UPSERT is, an UPSERT is an UPDATE and INSERT into a given table. Typically, an UPSERT operation requires two separate transactions. Usually, the first transaction issued is the UPDATE and then the INSERT is issued shortly after. In this post I will be extending the package I created, in my previous post, http://jahaines.blogspot.com/2009/09/ssis-only-inserting-rows-that-do-not.html. Once your SSIS package resembles the package in the link, you can proceed with the next step. If you want to skip the prior post, you can download the package at the bottom on this post.

The first item of interest is the Lookup transformation. First I will drag an OLE DB Command onto the canvas and connect it to the Lookup transformation. A dialog box should appear. When the dialog box appears choose the Lookup Match Output, in the Output dropdown list. An OLE DB Command, is a parameterized TSQL statement that can issued against a given data set. Before I start configuring the transformation, I would like to point out that this method is extremely easy to setup, but has a major drawback. The OLE DB Command has to be issued for each row returned by the Input, so this type of process works recursively or iteratively. I will demonstrate another method later in this post. With that out of the way, I will start configuring the transformation. First, I will build an UPDATE command using parameters for each column and the predicate.

My canvas currently looks like the screenshot below.

The next step is to configure the OLE DB Command transformation. Double-click the OLE DB Command transformation. In the Connection Manager dropdown list, choose the database where you created dbo.SSIS_Import. Click the “Component Properties” tab. Within the “Component Properties” tab, you will need to click the ellipses next to the “SQL Command” property. Paste the code presented below into the box and click ok.

UPDATE dbo.SSIS_Import

SET 

     SomeInt = ?,

     SomeChar = ?

FROM dbo.SSIS_Import import

WHERE SomeId = ?

The code above is a pretty simplistic UPDATE statement. The key thing to note is the “?”. Each of the ‘?” will be given a parameter value in SSIS. The parameters are dynamically named in the order they appear in the command. Next, I will be mapping these parameters to my input columns. Click the “Column Mappings” tab. Align the input columns to each parameter. My column mapping is shown below. Once complete, click “Ok.”

That it!!! We have successfully created an UPSERT operation using SSIS… but wait what is the catch? The catch is the UPDATE will operate like a cursor processing one UPDATE command at a time, which can be a nightmare from a performance standpoint. You may be wondering how we can make this process set based. There is not much you can do in SSIS alone, so I will need to find other means. The best way to make this process more scalable is to leverage SQL Server and SSIS.

The first step is to drop the OLE DB command I just created. I will then drag a OLE DB Destination to the canvas and connect it to the Lookup Match Output. My canvas looks like below.

Next, I will switch gears and write some TSQL code. I will need to open SSMS and connect to the database where dbo.SSIS_Import exists. Firstly, I have to create a view. I will not use the view to select data, but will use the view as a intermediate object to insert data.

CREATE VIEW vw_SSIS_Import

AS

SELECT [SomeId],[SomeInt],[SomeChar]

FROM dbo.[SSIS_Import]

GO

Now that my view is created, I am going to create an INSTEAD of trigger, on my view. The instead of trigger will allow me to use my OLE DB Destination to bulk insert from SSIS. The bulk insert from SSIS gives me a mechanism to pass the rows for UPDATE, as a set of data. Once I have the rows in a set, the trigger can efficiently UPDATE the data.

CREATE TRIGGER trg_UPdate_SSIS_Import

ON dbo.vw_SSIS_Import

INSTEAD OF INSERT

AS 

BEGIN

    UPDATE import

    SET 

        SomeInt = i.SomeInt,

        SomeChar = i.SomeChar

    FROM dbo.SSIS_Import import

    INNER JOIN inserted i

        ON i.SomeId = import.SomeId

END

GO

In the code above, I am using efficient TSQL to update all rows that are inserted into the view, from the SSIS package. I am now ready to configure the OLE DB Destination. Double-click the OLE DB Destination to launch the configure dialog box. Make sure your package is using the right connection manager and choose the view. Click the “Column Mappings” tab and then click '”Ok.” Right-click the OLE DB Destination and choose properties. In the “Fast Load Options”, type “FIRE_TRIGGERS.” This allows the SSIS insert to fire the INSTEAD OF trigger, on the view. That’s it we are done!!! The view/INSTEAD OF TRIGGER method is extremely easy to configure and implement; however, it does require that additional database objects to be created.

There you have it. I have demonstrated two methods to UPSERT data, using SSIS. The first method relies on the OLE DB Command, which has the limitation of having to run for each and every row. The latter option is a more scalable solution that requires the creation of a view and trigger. Both methods have pros and cons and I leave it to you to determine which is best for your environment.

Until next time, Happy Coding.

PS: If you want to do all of the work in SQL, you can allow the trigger to perform the UPDATE and INSERT. This method reduces the complexity of the SSIS package because you only need a single source and destination, with no Lookup.

Download SSIS Package:

• http://cid-6f041c9a994564d8.skydrive.live.com/self.aspx/.Public/SSS%20%e2%80%93%20Performing%20An%20UPSERT/SSS%20%e2%80%93%20Performing%20An%20UPSERT.zip

Note: The package was created in BIDS 2008, which is not compatible with BIDS 2005.

SSIS – Only Inserting Rows That Do Not Exist

I have seen an overwhelming trend that suggests that today’s TSQL/BI developers are very interested in using Microsoft’s BI (Business Intelligence) product, SQL Server Business Intelligence Development Studio aka BIDS.  For those of you who may not know, BIDS is installed when SQL Server is installed.  BIDS is nothing more than a Visual Studios add-in that uses the .Net Framework.   BIDS allows you to do Analysis Services (Cube Design and management),  Reporting Services, and Integration Services.  The BI platform that I will be focusing on today is SSIS (SQL Server Integration Services).  SSIS is used for ETL, which is a acronym for  Extract- Transform-Load.  Essentially SSIS is used to migrate data from many different sources,  including MS Access, Excel, Text Files, Oracle, SQL, AS400 etc, while allowing data transformations between source and destination.  The problem that most import processes have is flexibility.  Some bulk import APIs do not have an easy method to import new data, or data that does not currently exist in a table.  In these cases, the import process has to import all source data, then another command has to be issued to filter and insert the data.  A great example of this is BCP or the Bulk Insert command.  SSIS gives developers an easy and efficient method to insert new data.  This is where I will be focusing my efforts today, in a future post, I will demonstrate how to do an UPSERT (Update existing and Insert new) operation using SSIS.  Okay let’s start by creating a sample table.

--Switch DB context to Tempdb

USE [tempdb]

GO

SET NOCOUNT ON;

GO

--Drop table if exists

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

BEGIN

    DROP TABLE dbo.SSIS_Import;

END

GO

--Create table

CREATE TABLE dbo.SSIS_Import(

SomeID INT PRIMARY KEY,

SomeInt INT,

SomeChar CHAR(1)

);

GO

--Insert some test data

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (1,1,'A');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (2,5,'B');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (3,8,'C');

INSERT INTO dbo.SSIS_Import ([SomeID],[SomeInt],[SomeChar]) VALUES (4,2,'D');

GO

Now that our table exists, I am going to shift gears and start creating the SSIS package. Before creating the package, you should create a Pipe delimited text file on the C: drive, called SSIS_Import.txt.  Below is a sample of my file.

1|100|U

2|200|V

5|300|W

8|400|X

9|500|Y

10|600|Z

Okay now we can start creating our SSIS package.  You should now be looking at a blank canvas, the first objective is to drag a Data Flow Task onto the canvas.  Your canvas should look like below.

Note: I have renamed my data flow task to represent what it is doing.

I will now configure the Data Flow Task.  Double-click the data flow task to open the data flow task designer canvas. Drag a “Flat File Source” to the canvas.  Double-click the Flat File Source and create a new connection.  Your connection should be configured as shown below.

You will see an error message at the bottom on the window, stating the columns are not defined.  All you need to do is click the Advanced tab and make sure the data types and length are correct.  Column 0 and column 1 should be of type “four byte signed integer” and column 2 is a string with a length of 1.   In addition to configuring the data types, you should rename the columns.  You can change the name of a column by typing a new name in the Name property field.  The names should align with the table, so the mappings should look like this:

• Column 0 –> SomeId
• Column 1 –> SomeInt
• Column 2 –> SomeChar

After all configurations, your configuration should look like below.

Once the Flat File Source is configured, click “Ok” and “Ok” again.  The next step is to drag a “Lookup” transformation to the canvas and connect the Source connection to the Lookup transformation via the green precedence constraint.  Double-Click the Lookup transformation to configure it.  The first thing I have to do is change how errors are handled.  By default, if a single value is not found in the source, the entire Lookup component fails.  We will need to make sure non matching rows are redirected to the error output, as shown below.

Next, I will configure the connection.  Click the connection tab and the “New” button again.  The OLE DB connection should point to the database where the SSIS_Import table is.  Your screen should look like below.

Next we have to configure the key columns.  For the sample table the key column is SomeID, which maps to the Column 0, or SomeID if you renamed the column in your flat file connection manager, as shown below.

Note: To create the relationship you will have to drag the source column to the destination column.

Once you have finished configuration of this component, click “Ok” to save the changes.  Your canvas should be look like the screenshot below.

Next drag a OLE DB Destination to the canvas.  Make sure you drag the red arrow (precedence constraint) to the OLE DB Destination, which should cause a dialog box to popup. When it does just click “Ok.”  The destination controls where the source data will be inserted.  Double-click the OLE  DB Destination and make the destination point to the SSIS_Import table, as shown below.

Now click the mappings tab to map the source columns to the destination columns.  I have renamed my source columns, so SSIS will automatically map the columns for me. Click “Ok”, once you have mapped all the columns.

That is it!!! The final canvas will look like the screenshot below. 

All that is left is testing.  Click the debug button (looks like a “Play” button). If you have correctly configured all the steps,  the components will light up green.  If any turn red, something is not configured properly. Once all components light up green, run a simple select statement over the table to make sure it did what is supposed to do.

There you have it a method that takes less than 5 minutes to configure and will migrate new data only.  I hope that you have enjoyed reading this post and can make use of this in your environment. Stay tuned…… my next step is to Update pre-existing rows, using SSIS.  This is commonly known as an UPSERT.  I will show you a few ways to accomplish this task and the performance considerations, for each method.

Until next time… Happy Coding.

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!!