World of Whatever

EzAPI Overview

2012-01-30T08:00:00.000-06:00

I started looking at EzAPI for programmatically creating SSIS packages and I think it's an efficient product to work with. I have built SSIS packages purely through code before and up to a point, it's fun. After that, swimming in the ocean with a bad case of road rash is to be preferred.

Prerequisites

Pre-reqs are simple, you need to have something that can compile code and the EzAPI assembly.

Visual Studio 2008/Visual Studio 2010/SharpDevelop
SQL Server 2008 SSIS libraries (C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies). This might be optional, I have not tried, but it would depend on your usage.
EzAPI library. Start with the installer but you'll most likely want to step up to the source code if the base functionality doesn't scratch your itch
- EzAPISetup.msi installer
- EzAPI source code

Getting Started with EzAPI

Create a new project, I went with Console application as it's my standby. Anything that "runs" will work.

From EzAPI

Inside that project, add a reference to the following DLLs from the .NET tab.

From EzAPI

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.DTSRuntimeWrap

EzAPI

Where are my references?

If you don't see those DLL's listed, look at the message that says this list is filtered. No problem, just change the target framework from 4.0 Client profile to the normal one.

From EzAPI

Right-click on the project and select Properties. Change the Target framework from ".NET Framework 4 Client Profile" to ".NET Framework 4".

No, really, where are they?

As you may be able to tell, I started writing this post using VS2010 as I'm trying to make it my default VS instance now that Denali/Engine of the Devil/SQL 2012 is coming. The only problem is, with 2010 they've changed the way Visual Studio handles references. I gave up trying to make heads or tails out of it and just went and added the Assemblies via the Browse tab. It's not idea but I no longer care, I just want it resolved.I went in and manually added

c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
c:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Samples\EzAPI\EzAPI.dll

If you are not a 64 bit OS, then omit the " (x86)" path in this series.These two articles cover a bit of what they were doing with VS2010 and references but that will be my adventure for another evening.

http://blogs.msdn.com/b/jason_howell/archive/2010/08/18/visual-studio-2010-solution-build-process-give-a-warning-about-indirect-dependency-on-the-net-framework-assembly-due-to-ssis-references.aspx

http://msdn.microsoft.com/en-us/library/wkze6zky.aspx

On with the show

Code

I'm using C# here but any .NET language will suffice. For your first taste of the EzAPI, we'll start dirt simple and create a package that has nothing in it. Yes, be still your beating heart. We'll assign two properties as the package level and save to a file. The only thing that is required is to add the EzAPI namespace to our project (Microsoft.SqlServer.SSIS.EzAPI) and then start referencing a bevy of Ez* named classes.

On line 36 we declare a variable of type EzPackage and instantiate it on line 38. Lines 41 and 42 we assign values to the properties and on 43 we save the object to a file on disk. Once the code has executed, add the resulting package into a .dtproj and you have created your first package without using BIDS/SSDT.

   1:  //-----------------------------------------------------------------------

   2:  // <copyright file="Driver.cs" company="billfellows.net">

   3:  //     I mention copyright so StyleCop is happy.

   4:  // </copyright>

   5:  //-----------------------------------------------------------------------

   6:  namespace EzAPIRecipies

   7:  {

   8:      using System;

   9:      using System.Collections.Generic;

  10:      using System.Linq;

  11:      using System.Text;

  12:      using Microsoft.SqlServer.Dts;

  13:      using Microsoft.SqlServer.SSIS.EzAPI;

14:

  15:      /// <summary>

  16:      /// Driver class to demonstrate using EzAPI to build SSIS packages

  17:      /// </summary>

  18:      public class Driver

  19:      {

  20:          /// <summary>

  21:          /// This is the GO button.

  22:          /// </summary>

  23:          /// <param name="args">Command line arguments</param>

  24:          public static void Main(string[] args)

  25:          {

  26:              MakeSimpleEzAPIPackage();

  27:          }

28:

  29:          /// <summary>

  30:          /// Build the most basic of SSIS packages. Assign a name and description to the package and

  31:          /// nothing else.

  32:          /// </summary>

  33:          public static void MakeSimpleEzAPIPackage()

  34:          {

  35:              string outputFile = string.Empty;

  36:              EzPackage ezPackage = null;

  37:              outputFile = @"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\SimpleEzAPIPackage.dtsx";

  38:              ezPackage = new EzPackage();

39:

  40:              // Assigning a Description leads to a value of <EzName></EzName>Content here

  41:              ezPackage.Description = "I was built using EzAPI";

  42:              ezPackage.Name = "SimpleEzAPIPackage";

  43:              ezPackage.SaveToFile(outputFile);

  44:          }

  45:      }

  46:  }

One thing you can observe is the values written to those properties. Name has SimpleEzAPIPackage but look at the Description, that's not what we assigned. There are quirks in the library and this is one of them.

From EzAPI

As a contrast, the following code represents building the same package using the shipped assemblies. There's little difference between the two sets of code, beyond of course the second correctly assigns the Description...


        /// <summary>
        /// This is the classic approach to building a package
        /// </summary>
        public static void MakeSimplePackage()
        {
            string outputFile = string.Empty;
            Package package = null;
            Application app = null;

            outputFile = @"C:\sandbox\SSISHackAndSlash2008\SSISHackAndSlash2008\SimplePackage.dtsx";
            package = new Package();
            app = new Application();

            package.Description = "I was built using the straight .NET api";
            package.Name = "SimplePackage";
            app.SaveToXml(outputFile, package, null);
        }

Finally, you could have simply right-clicked on the project, selected Add new package, clicked Properties and assigned these values but everyone knows that route.

Convinced of the cost savings yet? Of course not, tune in to the rest of the series to see cost benefit of using EzAPI.

The worst query I ever wrote

2011-11-29T08:00:00.000-06:00

I work in an unusual environment---not everything is documented. I know, that sounds too unlikely to be true but it is. There was data in the database that needed to be reported on but not through the supplied UI. In this case, the specifics was the data was stored in Tfs_DefaultCollection and somewhat reportable through Visual Studio Team Explorer but the users wanted queries and reports that weren't supported through the API.

Enter my big mouth saying it's a database I can find a way to make it work

After randomly picking tables and scrolling through values, I needed a smarter approach. Smartness for me meant using the system meta data to find any column that could hold my data (right data type and minimum length). I then build and execute a query looking for an example of the search key in that column. Below is an example of a search for the word change. What I like about this query, is it ought to be fairly portable/database agnostic as I'm using the INFORMATION_SCHEMA views to identify the candidate schemas, tables and columns.



SET NOCOUNT ON;

DECLARE
    -- actual query 
    @query nvarchar(max)
    -- templated query
,   @template nvarchar(max)
    -- string value we wish to find
    -- Length of 50 is arbitrary
,   @searchKey varchar(50);

SET
    @searchKey = 'change';

-- Return column & schema/table combo anywhere
-- there is data in the column that starts like the
-- search key.
SELECT
    @template = N'
    SELECT TOP 1 
        ''<COLUMN_NAME/>'' AS cname
    , ''<SCHEMA/>.<TABLE_NAME/>'' AS tname 
    FROM 
        <SCHEMA/>.<TABLE_NAME/> T 
    WHERE 
        T.<COLUMN_NAME/> LIKE ''<TARGET/>%''';

SELECT
    @template = REPLACE(@template, '<TARGET/>', @searchKey);

DECLARE 
    CSR CURSOR
FOR
-- Iterate through all the columns that are
-- character data types and are at least
-- as long as the search key
SELECT
    ISC.TABLE_SCHEMA
,   ISC.TABLE_NAME
,   ISC.COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS ISC
WHERE
    -- filter out tables/views I know I can skip
    ISC.TABLE_NAME not like 'x%'
    AND
    (
        ISC.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
        AND ISC.CHARACTER_MAXIMUM_LENGTH >= LEN(@searchKey)
    );

-- Cursor variables for capturing candidate schemas, tables and columns
DECLARE
    @table_schema sysname
,   @table_name sysname
,   @column_name sysname;

DECLARE
    @RESULTS TABLE
(
    column_name sysname
,   table_schema nvarchar(500) NOT NULL
);

OPEN
    CSR;

FETCH NEXT
FROM
    CSR
INTO
    @table_schema
,   @table_name
,   @column_name;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- stub in actual names, make 'em safe via quotename function
    SET @query = REPLACE(@template, '<SCHEMA/>', quotename(@table_schema));
    SET @query = REPLACE(@query, '<TABLE_NAME/>', quotename(@table_name));
    SET @query = REPLACE(@query, '<COLUMN_NAME/>', quotename(@column_name));

    BEGIN TRY
        --PRINT @query
        
        -- Dump results into a table variable
        INSERT INTO
            @RESULTS
        EXECUTE(@query);
        
    END TRY
    BEGIN CATCH
        -- print failing query
        PRINT @query;
        
    END CATCH
    FETCH NEXT
    FROM
        CSR
    INTO
        @table_schema
    ,   @table_name
    ,   @column_name;
END
CLOSE CSR;
DEALLOCATE CSR;

-- Show all the columns and fully qualified tables
-- that contained the value
SELECT 
    R.column_name
,   R.table_schema
,   'SELECT T.* FROM ' + R.table_schema + ' T WHERE T.' + R.column_name + ' LIKE  ''' + @searchKey + '%''' AS explore_query
FROM 
    @results R;

I can run the above against Tfs_DefaultCollection.


column_name table_schema explore_query
[ChildItem] [dbo].[tbl_Version] SELECT T.* FROM [dbo].[tbl_Version] T WHERE T.[ChildItem] LIKE  'change%'
[ProjectName] [dbo].[tbl_Project] SELECT T.* FROM [dbo].[tbl_Project] T WHERE T.[ProjectName] LIKE  'change%'
[ChildItem] [dbo].[tbl_VersionedItem] SELECT T.* FROM [dbo].[tbl_VersionedItem] T WHERE T.[ChildItem] LIKE  'change%'

And easily switch over to Tfs_Warehouse to try and track those values over there


column_name table_schema explore_query
[ChangesetTitle] [dbo].[DimChangeset] SELECT T.* FROM [dbo].[DimChangeset] T WHERE T.[ChangesetTitle] LIKE  'change%'
[FileName] [dbo].[DimFile] SELECT T.* FROM [dbo].[DimFile] T WHERE T.[FileName] LIKE  'change%'
[ProjectNodeName] [dbo].[DimTeamProject] SELECT T.* FROM [dbo].[DimTeamProject] T WHERE T.[ProjectNodeName] LIKE  'change%'

Warnings, caveats, etc

While I'd think it's blindingly obvious, never run code from the interblag. Beyond that, this code is not going to be fast by any stretch of the imagination. It could lock a table for the read. It might bog down your disk infrastructure, especially if you're querying against a very large table. And it's going to do it over and over again so buyer beware.

Filter list in SSIS

2011-11-28T08:00:00.000-06:00

I saw the question asked, how can I split/filter a data flow based on an array-like data source.. I thought that sounded like a decent question so I investigated some alternate approaches.

Problem definition

Given an iterable/sequence, what are the options for using that reference data as a filter in SSIS? To test this, I created a reference list of US States and their FIPS code as well as an indicator of their allegiance in the Civil War. There's no significance to the data beyond it was a publicly available dataset. I threw against that reference set 60 numbers (monotonically increasing values) to how it could be accomplished. As there are only 50 states and not all were in existence during the Civil War, I expect my data to be split into four buckets: Union, Confederate, Undeclared (valid FIPS code but not in existence), Unknown (invalid code).

Package Setup

I created 3 sets of variables (Confederate, Union and Undeclared) in 2 flavors (String and Object).

I have a script that runs that populates them.


public void Main()
{
    // User::ConfederateList,User::ConfederatesStates,User::UndeclaredList,User::UndeclaredStates,User::UnionList,User::UnionStates
    List<int> Confederates = new List<int> { 01, 05, 12, 13, 22, 28, 37, 45, 47, 48, 51 };
    List<int> Union = new List<int> { 06, 09, 10, 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 29, 32, 33, 34, 36, 39, 41, 42, 44, 50, 54, 55 };
    List<int> Undeclared = new List<int> { 02, 04, 08, 15, 16, 30, 31, 35, 38, 40, 46, 49, 53, 56 };

    this.Dts.Variables["ConfederateList"].Value = Confederates;
    this.Dts.Variables["ConfederatesStates"].Value = string.Format(":{0}:", string.Join(":", Confederates.ConvertAll<string>(delegate(int i) { return i.ToString(); }).ToArray()));

    this.Dts.Variables["UnionList"].Value = Union;
    this.Dts.Variables["UnionStates"].Value = string.Format(":{0}:", string.Join(":", Union.ConvertAll<string>(delegate(int i) { return i.ToString(); }).ToArray()));

    this.Dts.Variables["UndeclaredList"].Value = Undeclared;
    this.Dts.Variables["UndeclaredStates"].Value = string.Format(":{0}:", string.Join(":", Undeclared.ConvertAll<string>(delegate(int i) { return i.ToString(); }).ToArray()));

    Dts.TaskResult = (int)ScriptResults.Success;
 }

Once this script has completed, these variables will look like

I also use this script as the source in my Data Flow tasks.


public override void CreateNewOutputRows()
{
    for (int i = 0; i < 60; i++)
    {
        Output0Buffer.AddRow();
        Output0Buffer.StateCode = i;
    }
}

Conditional split

The Conditional Split transformation and the expression language itself does not offer any lookup type operation so a complex object like an array or List is out of the question. However, I was able to elicit a lookup-like functionality by using the FINDSTRING backwards. Normally, I think of using it as FINDSTRING(MyColumn, "SomeValue", 1). That is, if SomeValue exists anywhere in MyColumn, the return value would be the one-based position where the value is found, zero for no match.

However, if you think of it opposite, you could use the reference value as the first argument and the current value in the second. Let's put some values to put this thinking in perspective. Missouri's code is 29. The set of Union states is 6, 9, 10, 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 29, 32, 33, 34, 36, 39, 41, 42, 44, 50, 54, 55. If I could find a way to represent the Union states set in a string format, I could use FINDSTRING on it. The first problem you'll run into though, is California aka 6. 6 will match 6 but it will also match 26 (Michigan), 36 (New York). We'd luck out in that scenario as they're both Union states. Idaho, 16, would end up being tagged as Union state even though they would not achieve statehood until 25 years after the Civil War. Same story with 46---South Dakota and 56---Wyoming, they were not around as states to cast their lot in the war. 60 too would be recognized as a Union state.

For my data set, I am restricting the FIPS State set to only the 50 states currently recognized. The standard itself recognizes many more entities than just the 50 though so 60 is assigned to American Samoa. 60 should be kicked out to the Unknown bucket based on our input set. Clearly we need to do something more than search for our term, we need to give our search the concept of a word boundary. Using a regular expression, it'd be as easy as using \b. Here, we'd need to introduce our own artificial word boundaries. Chose a value that should never appear in your source data. I chose to delimit everything with a colon. Thus, the above set would be represented as :6:9:10:17:18:19:20:21:23:24:25:26:27:29:32:33:34:36:39:41:42:44:50:54:55: I would then need to modify my input value by concatenating a leading and trailing colon. (FINDSTRING(@[User::UnionStates],":" + (DT_WSTR,2)StateCode + ":",1)) > 0

I chose to create derived columns before the conditional split but the logic could just as easily been performed within the Conditional split itself.


(FINDSTRING(@[User::ConfederatesStates],":" + (DT_WSTR,2)StateCode + ":",1)) > 0
(FINDSTRING(@[User::UnionStates],":" + (DT_WSTR,2)StateCode + ":",1)) > 0
(FINDSTRING(@[User::UndeclaredStates],":" + (DT_WSTR,2)StateCode + ":",1)) > 0

Complex objects

Conditional split works good enough, I suppose but it smells. Maintaining that logic is not going to be pleasant and there's no good way to know if your delimiter character suddenly becomes part of your source data. If it's purely numeric, then yes, you can figure that out but if the task is to find strings in a set of strings, that can get tricky. What would be nice is to use a something cleaner like set based logic for finding membership.

To make this work, we need to use the script transformation but possibly in a way you've never used it.

This seems simple enough, remarkably like our conditional split approach. The major difference though is this script task is asynchronous. This means the data in our buffers are going to be copied (expensive) from the input buffer to the corresponding output buffer. I also needed to create the various output buffers and define the shape of the data. That's time consuming and nothing I'd care to maintain.

The code is straight forward. Create 3 List variables for the class, instantiate them in the PreExecute and then use the value of the current row in the Contains method of the class objects. Once we find a match, create a row on the appropriate buffer and fill it with data.


private List<int> Confederates;
private List<int> Union;
private List<int> Undeclared;

public override void PreExecute()
{
    base.PreExecute();
    this.Confederates = this.Variables.ConfederateList as List<int>;
    this.Union = this.Variables.UnionList as List<int>;
    this.Undeclared = this.Variables.UndeclaredList as List<int>;
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (this.Confederates.Contains(Row.StateCode))
    {
        ConfederatesBuffer.AddRow();
        ConfederatesBuffer.StateCode = Row.StateCode;
    }
    else if (this.Union.Contains(Row.StateCode))
    {
        UnionBuffer.AddRow();
        UnionBuffer.StateCode = Row.StateCode;
    }
    else if (this.Undeclared.Contains(Row.StateCode))
    {
        UndeclaredBuffer.AddRow();
        UndeclaredBuffer.StateCode = Row.StateCode;
    }
    else
    {
        UnknownBuffer.AddRow();
        UnknownBuffer.SateCode = Row.StateCode;
    }
}

As an afterthought, we could make this identical in execution by offloading the conditional split logic to the native component and simply used a script task in synchronous fashion, much like we did for the Conditional Split's Derived Column transformation "Assign values". This would actually be a better approach all around in terms of resource usage, processing speed, maintenance, etc.

Cached Connection Manager

I must confess, until recently, I never thought about CCM. I cut my teeth on SQL Server 2005's SSIS so some of the "new" features aren't as ingrained in my problem solving toolbox like they should be. CCM is one I've definitely overlooked but am coming around to appreciating it's utility. This approach will have us load the available values into a cached connection manager which we will then leverage in the subsequent data flow to use the native Lookup task to determine values.

This post runs long as it is so I won't show the whole code but there are 250 lines of this code enumerating all the states and their affiliation. When executed, that data is sent to a cache connection manager for later consumption.


Output0Buffer.AddRow();
Output0Buffer.StateName = "Alabama";
Output0Buffer.StateAbbreviation = "AL";
Output0Buffer.StateCode = 01;
Output0Buffer.SideDeclared = "C";

Output0Buffer.AddRow();
Output0Buffer.StateName = "Alaska";
Output0Buffer.StateAbbreviation = "AK";
Output0Buffer.StateCode = 02;
Output0Buffer.SideDeclared_IsNull = true;

I use the Lookup task to retrieve the SideDeclared value. I "redirect rows to no match output" to my Unknown bucket. The found rows are sorted into their appropriate bucket with a conditional split.

Again, for consistency I could have also sent the unknown rows down to the Conditional Split (Ignore failure option) and done all the redirects there. My reason for not doing so here is laziness. I saw no need to write the conditional split logic when the native component already offered it.

Performance

I scaled up to 60 million rows and used the modulus operator on the (%67) on the source script for sending data to ensure we were sending consistent batches to all the options. I added row counts to all the output so the optimizer didn't factor branches out and ran it five times from the command-line (all in 32 bit mode). As a last minute test, I'm rerunning the processing in 64 bit mode.

Conditional Split list: This is the approach described above abusing FINDSTRING.
Smarter Script: This is the synchronous version of my script working against the List objects
Script task: This is the asynchronous version of my script working against the List objects
Use lookups: This uses the Cache Connection Manager

Source	Rows	Average task duration (ms)	σ task duration	Average throughput (rows / ms)	σ througput	Mode
Conditional Split list	60,000,000	337,800	97688.08	192	49.79	32bit
Conditional Split list	60,000,000	254,500	4500	235.83	4.17	64bit
Smarter Script	60,000,000	458,600	11,038.12	130.91	3.14	32bit
Smarter Script	60,000,000	429,500	30,500	140.41	9.97	64bit
Script task	60,000,000	471,800	22,301.57	127.46	5.99	32bit
Script task	60,000,000	466,500	19,500	128.84	5.39	64bit
Use lookups	60,000,000	485,800	14246.4	123.61	3.66	32bit
Use lookups	60,000,000	457,500	11500	131.23	3.3	64bit

Conclusion

I am shocked on the performance results. For this problem domain, the FINDSTRING approach was the most efficient, by a considerable margin. The other three approaches consistently averaged a throughput of within 7 rows per millisecond of each other. I did find it interesting that the standard deviation of the FINDSTRING approach fluctuated so much. While this box is older and slower, there was not a considerable amount of activity going on during the package executions.

64 vs 32 bit

With a sample size of 2, all the 64 bit tasks saw an increase in throughput over their 32 bit counterparts. FINDSTRING method showed the most improvement, it's now nearly 100 rows/ms faster than the next component. Interestingly enough, the asynchronous script task saw a middling 1 row/ms increase compared to the nearly 10 row/ms increase on the other two tasks. I'll collect more data and see if the pattern holds true.

My package is available on my google site for your own evaluations.

SSIS Package Extract from MSDB

2011-11-22T08:00:00.000-06:00

A question came up on StackOverflow of how someone can retrieve their packages from the MSDB. In this case, their local development went south and they only had the packages in production. Let this be an object lesson in using version control.

To extract packages from the MSDB, you must first identify where in the msdb they exist. For that, you can query sysssispackagefolders and sysssispackages or you can just use my query SSIS Package Query

Armed with that query, the column of interest is the PackagePath column. Couple that with dtutil and you have an extract-o-matic for package recovery.

The base form of an extract from MSDB on localhost to the current folder in the file system would look like.

dtutil /sourceserver localhost /SQL "Package" /copy file;.\Package.dtsx

Extract-o-matic

Run this query in Text mode (ctrl-T) This query generates a series of dtutil calls which in turn extracts SSIS packages from a server.


;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT 
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename') 
    -- if you need to really make this generic
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;.\' + P.PackageName +'.dtsx' AS cmd
FROM 
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
-- uncomment this if you want to filter out the 
-- native Data Collector packages
-- WHERE
--     F.FolderPath <> '\Data Collector'

For me, that generated the following code.


dtutil /sourceserver localhost /SQL "\Package" /copy file;.\Package.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersCollect" /copy file;.\PerfCountersCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\PerfCountersUpload" /copy file;.\PerfCountersUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityCollect" /copy file;.\QueryActivityCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\QueryActivityUpload" /copy file;.\QueryActivityUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceCollect" /copy file;.\SqlTraceCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\SqlTraceUpload" /copy file;.\SqlTraceUpload.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryCollect" /copy file;.\TSQLQueryCollect.dtsx
dtutil /sourceserver localhost /SQL "\Data Collector\TSQLQueryUpload" /copy file;.\TSQLQueryUpload.dtsx

Hooray, my bacon is saved and now I can put my packages into version control like a good developer.

PowerShell manipulation of SSIS packages

2011-11-11T08:00:00.000-06:00

One of the developers has been hand-coding some very repetitive SSIS packages. They perform a full replication of data out of an Informix database into our SQL Server. For the final database, he had about 50 tables that needed to be processed. I convinced him that we can make his life and maintenance better by creating lots of little packages vs the behemoth package with umpteen data flows in it.

I built out a template package, PolSumAgentCountyDir.dtsx, that is an Execute SQL Task, wired to a Data Flow. The trick is the expressions. I have one variable, base_table that drives the operation of the package. There are variables that build the delete statement, source query and destination table. If I change the value of base_table from foo to bar, the OLE DB datasource in the package is going to get very angry because I've swapped out the meta data on it. Back in the DTS days, I could have passed a list to the process and it'd have been able to make it work but this is SSIS and that doesn't fly

The original approach was we were going to split the list of tables to replicate and meet in the middle. Each person would open the template, update the value of base_table, save the package with a name based on the table it's replicating (along with the SSIS's package's Name property) and then fix the meta data. Dull work, but doable.

If I were clever, know what I'd do? I'd spend an hour watching StarTrek FY Edition and writing some PowerShell. Calling the Function below with a fully qualified path to my template package, the table name and the English name of the table (ls_pol_sum => LivestockPolicySummary) results it instantiating an instance of the SSIS object model, loading the template package and programmatically manipulating it and saving it out with a new name. Dead simple but until you try it, or see it, you won't believe how so. I spent more time defining the hash table with table names to English than I did the code for manipulating objects. I'm sure some of that's due to my familiarity with the object model from my PowerShell SSIS deployment and maintenance script.


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null

# Utility script for making clones of a template SSIS package
# and modifying the variable (base_table) that drives operation
# of the package
# Created by Bill Fellows
# 2011-11-10

Function ModifyTemplate
{
    param
    (
        [string]$template,
        [string]$tableName,
        [string]$PackageSuffix
    )
    
    $events = $null
    $userName = $null
    $password = $null

    try
    {
        # open template package
        # save as package suffix
        # replace AgentCountyDir in template with suffix
        $newName = $template.Replace("AgentCountyDir", $PackageSuffix)
        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application
        $package = $app.LoadPackage($template, $null)

        # modify the table variable
        $package.Variables["base_table"].Value = $tableName
        $package.Name = $package.Name.Replace("AgentCountyDir", $PackageSuffix)
        $app.SaveToXml($newName, $package, $events)
    
    }
    catch
    {
        Write-Error ([string]::Format("Failed to do something. Table {0} on server {1}", $tableName, $PackageSuffix))
        Write-Error $_ | fl * -Force
        exit(0)
    }

}

Who loves PowerShell? This guy

Find all the tables without a clustered index

2011-11-04T08:00:00.000-05:00

We were having performance issues at work and I got to take a quick look at it. Performing multiple table scans against a heap is not good. When that heap is 6+ million rows, not good becomes atrocious. Using a pair of queries, I put together the following.

Run this on any server and it'll spit back a report listing all the tables per database that have no clustered index and their associated row counts. Don't worry, the row count query is lightning fast as it isn't physically counting rows. Instead it's using a DMV so SQL Server 2005+ only please



DECLARE @database_name sysname  
DECLARE
    @REPORT TABLE
(
    server_name sysname
,   database_name sysname
,   schemaname sysname
,   table_name sysname
,   row_counts bigint
)
    

DECLARE Csr CURSOR FORWARD_ONLY STATIC FOR  
SELECT DB.[name] 
FROM master.dbo.sysdatabases DB
WHERE DB.[name] NOT IN ('tempdb'
,'master'
,'model'
,'msdb'
,'ReportServer'
,'ReportServerTempDB'
)

DECLARE @queryTemplate nvarchar(max) = N'
USE <DB_NAME/>;
WITH OF_INTEREST AS
(
    SELECT DISTINCT 
        @@servername AS server_name
        , DB_NAME(DB_ID()) AS database_name
        , schema_name(T.schema_id) AS schemaname
        , OBJECT_NAME(I.object_id) AS table_name
    FROM SYS.INDEXES I
    INNER JOIN
        sys.tables T
        ON T.object_id = I.object_id
    WHERE INDEX_ID = 0
    AND OBJECTPROPERTY(I.object_id,''IsUserTable'') = 1
)
, RC AS
(
    SELECT
        s.[Name] as [SchemaName]
    ,   t.[name] as [TableName]
    ,   SUM(p.rows) as [RowCounts]
    FROM 
        sys.schemas s
        LEFT JOIN 
            sys.tables t
            ON s.schema_id = t.schema_id
        LEFT JOIN 
            sys.partitions p
            ON t.object_id = p.object_id
        LEFT JOIN  
            sys.allocation_units a
            ON p.partition_id = a.container_id
    WHERE 
        p.index_id  in(0,1) -- 0 heap table , 1 table with clustered index
        AND p.rows is not null
        AND a.type = 1  -- row-data only , not LOB
    GROUP BY 
        s.[Name]
    ,   t.[name]
)
SELECT
OI.*
, RC.RowCounts
FROM
OF_INTEREST OI
INNER JOIN
    RC
    ON RC.schemaname = OI.schemaname
    AND RC.tablename = OI.table_name
    
'
, @query nvarchar(max)


OPEN Csr  
FETCH NEXT FROM Csr INTO @database_name  
WHILE @@FETCH_STATUS = 0  
BEGIN

    SELECT @query = replace(@queryTemplate, '<DB_NAME/>', @database_name)
    BEGIN TRY
        INSERT INTO
            @REPORT
        EXECUTE (@query)
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER()AS error_number --returns the number of the error.
        ,   ERROR_SEVERITY() AS error_severity --returns the severity.
        ,   ERROR_STATE()AS error_state  --returns the error state number.
        ,   ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
        ,   ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
        ,   ERROR_MESSAGE() AS error_message --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
    END CATCH

    FETCH NEXT FROM Csr INTO @database_name  
END  

CLOSE Csr  
DEALLOCATE Csr
;

SELECT * FROM @REPORT R

Thanks to Pinal Dave for the query to identify heaps and James Beresford for the fast row count query.

Using Excel in an SSIS lookup transformation

2011-11-02T08:00:00.000-05:00

There was a question on StackOverflow: Excel Source as Lookup Transformation Connection asking how one can use Excel as a source in a lookup. As I worked on the answer, I figured it'd be a good thing to post here so I'm copying and pasting my work. The important thing is that this approach isn't restricted to just the lookup task. If you are having trouble using Excel in SSIS as a normal data source because it thinks the data types have changed, access it via the OLE DB driver.

As you have correctly discerned, you cannot use the Excel Connection Manager in a Lookup task, it only accepts the OLE DB connection manager. The "normal" Excel connection isn't an OLE DB type. The trick then, is to use an OLE DB connection manager with an Excel spreadsheet.

For this example, I have a spreadsheet with state codes and their full name and my source data only has the abbreviations flowing through. This walk through will wire up a lookup task against Excel to retrieve those values.

Keep these caveats in mind: Lookups are case sensitive, regardless of whether the source database (or file in this case) is case sensitive. Excel strings are always going to be interpreted as unicode/nvarchar.

Source Data

Given a simple file sitting at C:\tmp\LookupReference.xlsx that looks like the following

Connection Manager

We must first establish an OLE DB Connection Manager. Instead of the default "Native OLE DB\SQL Server Native Client 10.0" change that to "Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider". In the Server or file name, locate the source file. Clicking Test Connection at this point will result in an error.

Here comes the "magic." Click the All tab and scroll to the top. In Extended Properties, add the indicated string "Excel 12.0;HDR=YES; IMEX=1;" This tells the provider that we are going to use Excel 12.0 (.xlsx format) with a header row and the IMEX 1 tells the driver there will be intermixed data.

Your package sould now look something like this. A connection manager with extended properties set and assumes a preexisting data flow

Data Flow

To simplify matters, I have a script source that generates 3 rows of data with state codes MO, KS and NE and sends them down the pipeline. Your source will obviously be different but the concept will remain the same. Sample code provided in the annotation.

In your lookup transformation, you will need to write a query against the spreadsheet. It's similar to a normal database query except your table is going to be Sheet1$ unless you have a named range in which your table would be MyRange Note the $ is required when referencing a sheet. Using the sample spreadsheet above, my query would be

SELECT
    S.StateCode
,   S.StateName
FROM 
    `Sheet1$` S

I map my Script task column StateCode to the reference query's StateCode column and check the StateName field as I want to add that to my data flow.

I've put a Data viewer after the Lookup task to verify my lookups worked

Everything works and we're all happy.

Old Excel

If you are using a .xls file, you need to make the following changes.- In your Connection Manager, instead of the Office 12 provider, select the "Native OLE DB\Microsoft Jet 4.0 OLE DB Provider"- The Extended Properties become "EXCEL 8.0;HDR=Yes; IMEX=1;"

Notes for 2008+

A tip of the hat to Matt Masson (Blog|Twitter) for reminding me of the Cached Connection Manager. In particular, he points out that "you get weird behavior if you have multiple lookups against the same file." So, if you are on 2008+, look at using the CCM.

Before you need the cached data, load it into the cache like so

The lookup basically remains the same. In the General tab, change the source to the cache connection manager and in the Connection tab, change to the CCM.

Excel Slicers won't resize

2011-10-24T11:16:00.001-05:00

This short post is in lieu of a longer post but if you create a slicer in Excel and attempt to resize it and it keeps snapping back to the tiny, unusable size, then read this Connect article There's a hidden bounding rectangle around the slicer window and that is what is causing it to shrink back.

What an amazingly painful thing to track down...

SQL Community

2011-10-18T22:59:00.000-05:00

In stark contrast to the #sqlidiot #sqlsue negativity flying about by some bad apples in the community, I wanted to share an email that I received yesterday from one of the people I met at the 2011 SQL PASS. At one of the lunches, I sat a random table of people and introduced myself. It turns out, I met two people from the same small town in Wyoming that my father-in-law lives in. How small? 5500 people and the biggest town for probably an hour. Total kismet that I sat there.

I probably sounded like an evangelist with all the talking I was doing but I kept emphasizing how great the community is and things they won't want to miss out on the Summit. I pimped Brent Ozar's Twitter book and said you need to get on there and get involved with the community. I spoke about finding a SQL Server User Group or starting their own and of course, told them about SQL Saturdays and how they could get more great content for free at them. Colorado Springs #104 is a longer drive but definitely a manageable option from where their town is.

We met at lunch on Tuesday at the PASS summit. I’m the DBA at the school district in X, WY. Your advice on how to get the most out of the conference was really valuable, thank you!

I got on Twitter like you suggested I should. I tweeted to find some other K-12 education SQL server people at the summit and it worked; on Friday, I was having breakfast with two other K-12 DBAs.

So I’m very happy to have made your acquaintance! You really opened my eyes to the great resource that the PASS community can be.

That completely made my day, heck it made my 2 days ;)

Your takeaway from all of this? Be excellent to each other

Adventures with OUTPUT, MERGE and INSTEAD OF triggers

2011-09-20T08:00:00.000-05:00

This StackOverflow question was really interesting for me.
Not because of the users question itself, I thought it was a terrible premise all around to assume that an index violation would lead to the population of an identity value. No, what I found fascinating was my dive into

Excluding the ignore duplicate key, I've used all of these to some extent in my professional career, but never tried to use them all at once.

Highlights of my findings

MERGE statement with OUTPUT clause seemed to offer the best fit for what the questioner wanted
OUTPUT will not have access to the inserted logical table even though the insert will have consumed an identity value and rolled back
You can fake it through an INSTEAD OF trigger but it's an ugly hack and you wouldn't able to take advantage of the OUTPUT clause

The inserted virtual table within an INSTEAD OF trigger has identity values populated with 0
You cannot update the inserted/deleted logical tables
The OUTPUT clause on a table with an instead of trigger operates on the virtual tables

-- This script demonstrates the usage of 
-- IGNORE_DUP_KEY option for an index
-- http://msdn.microsoft.com/en-us/library/ms186869.aspx
-- Why you'd want this behaviour is left as 
-- an excercise to the reader
--

SET NOCOUNT ON

IF EXISTS(SELECT 1 FROM sys.tables T WHERE T.name = 'DupesOk' AND T.schema_id = schema_id('dbo'))
BEGIN
    DROP TABLE dbo.DupesOk
END
GO

CREATE TABLE 
    dbo.DupesOk
(
    dupe_id int identity(1,1) NOT NULL PRIMARY KEY
,   name varchar(50) NOT NULL
)

-- Create an index that is unique but
-- violation of the unique constraint is
-- merely discarded with warning instead of
-- blowing up
CREATE UNIQUE INDEX 
    uq_dupes_name
ON dbo.DupesOk
(
    name
)
WITH IGNORE_DUP_KEY 

-- Add a name and emit the identity value
-- from the inserted virtual table
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Peter Parker'

-- Old-school means of showing the identity
-- values, 1's across the board
-- See earlier posting
-- http://billfellows.blogspot.com/2009/10/scope-and-identity.html
SELECT 
    @@IDENTITY AS six_of_one
,   SCOPE_IDENTITY() half_dozen_of_other
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


-- Add a new name and emit the identity value
-- from the inserted virtual table
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Spider man'

-- Same as above, 2s across the board
SELECT 
    @@IDENTITY AS six_of_one
,   SCOPE_IDENTITY() half_dozen_of_other
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

-- Insert a duplicate value for the unique index
-- watch it not explode with a output message of 
-- 'Duplicate key was ignored.'
INSERT INTO
    dbo.DupesOk
OUTPUT
    -- This won't show anything as there is nothing to show
    inserted.dupe_id
,   inserted.name
SELECT
    'Peter Parker'

-- The first two remain 2's as they belong to the successful
-- insert of Spider man. ident_current shows that the value was
-- incremented. The calling code did not do a lookup to  
SELECT 
    @@IDENTITY AS this_identity_belongs_to_spider_man
,   SCOPE_IDENTITY() this_identity_also_belongs_to_spider_man
    -- As expected, the value is now 3, it got incremented 
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

;
MERGE
    -- target table
    dbo.DupesOk AS T
USING
(
    -- source system
    SELECT 'Hal Jordan' AS name
) AS S
ON S.name = T.name
WHEN
    MATCHED THEN
    UPDATE
    SET 
        T.name = S.name
WHEN
    NOT MATCHED THEN
    INSERT
    (
        [name]
    )
    VALUES
    (
        [name]
    )
-- 4 | Hal Jordan | INSERT
OUTPUT
    inserted.dupe_id
,   inserted.name
,   $action
;


-- 4's as expected
SELECT 
    @@IDENTITY AS hal_jordan
,   SCOPE_IDENTITY() still_hal_jordan
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


-- Add someone else just to get the ids to flip
INSERT INTO
    dbo.DupesOk
OUTPUT
    inserted.dupe_id
,   inserted.name
SELECT
    'Tony Stark'

-- 5's
SELECT 
    @@IDENTITY AS tony_stark
,   SCOPE_IDENTITY() still_tony_stark
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value

;
-- Try inserting an existing id 
MERGE
    -- target table
    dbo.DupesOk AS T
USING
(
    -- source system
    SELECT 'Hal Jordan' AS name
) AS S
ON S.name = T.name
WHEN
    MATCHED THEN
    UPDATE
    SET 
        T.name = S.name
WHEN
    NOT MATCHED THEN
    INSERT
    (
        [name]
    )
    VALUES
    (
        [name]
    )
-- 4 | Hal Jordan | UPDATE
OUTPUT
    inserted.dupe_id
,   inserted.name
,   $action
;

-- Still 5's
SELECT 
    @@IDENTITY AS tony_stark
,   SCOPE_IDENTITY() still_tony_stark
,   IDENT_CURRENT('dbo.DupesOk') AS current_identity_value


GO
-- What if we try a trigger?
-- It would need to be an instead of trigger
-- as the value will have already been 
-- http://msdn.microsoft.com/en-us/library/ms189799.aspx
-- http://msdn.microsoft.com/en-us/library/ms175089.aspx
CREATE TRIGGER tr_dupes_insert
ON dbo.DupesOk
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    -- variety of different approaches here but
    -- I'll attempt the insert and if no rows
    -- are affected, then we know it's an existing
    -- row and lookup the identity
    DECLARE
        @ident TABLE
    (
        dupe_id int NOT NULL
    ,   name varchar(50) NOT NULL
    )

    -- Only n00bs code triggers for single rows
    INSERT INTO
        dbo.DupesOk
    (
        name
    )
    -- output clause
    -- http://msdn.microsoft.com/en-us/library/ms177564.aspx
    OUTPUT
        -- the output's virtual table
        -- recursion is deep, yo
        inserted.dupe_id
    ,   inserted.name
    INTO
        @ident
    SELECT
        I.name
    FROM
        -- the trigger's virtual table
        -- fascinatingly enough, the value for
        -- an identity field pre-insert on an
        -- instead of trigger is 0 and not NULL
        -- as one would assume
        inserted I

    -- Now we need to add anyone into the 
    -- table variable that didn't get inserted
    -- into @ident in the previous statement
    INSERT INTO
        @ident
    SELECT
        D.dupe_id
    ,   D.name
    FROM
        inserted I
        INNER JOIN
            dbo.DupesOk D
            ON D.name = I.name
        LEFT OUTER JOIN
            @ident tv
            -- can't match on ids here
            -- as they all come in as zero
            ON tv.name = I.name
    WHERE
        tv.dupe_id IS NULL

    SELECT
        I.dupe_id
    ,   I.name
    FROM
        @ident I

    -- To make OUTPUT work correctly, we'd need to
    -- "fix" the values in the inserted virtual tables
    -- but uncommenting this will result in a 
    -- trigger creation error of
    -- "The logical tables INSERTED and DELETED cannot be updated"
    --UPDATE 
    --    I
    --SET
    --    dupe_id = -1
    --FROM
    --    inserted i
    --    INNER JOIN
    --        @ident TV
    --        ON TV.name = i.name
END
GO

DECLARE
    @idents TABLE
(
    dupe_id int 
,   name varchar(50)
)


-- We should see
-- 1 | Peter Parker
-- 7 | Barry Allen
--
-- 6 was consumed by the double pump of Hal Jordan

-- results were surprising, to me at least


INSERT INTO
    dbo.DupesOk
-- this will generate an error
-- The target table 'dbo.DupesOk' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
-- unless we dump output results into a table
OUTPUT
    inserted.dupe_id
,   inserted.name
INTO
    @idents
SELECT
    'Peter Parker'
UNION ALL
SELECT
    'Barry Allen'

-- The above statement's trigger correctly spits out the rows we emit in the final
-- step of the trigger
-- dupe_id  name
-- 7        Barry Allen
-- 1        Peter Parker

-- Look at this, it's the inserted virtual table
-- from the trigger in pristine condition
-- and there's no way to unbugger it
SELECT * FROM @idents I

Results

dupe_id     name
----------- --------------------------------------------------
1           Peter Parker

six_of_one                              half_dozen_of_other                     current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
1                                       1                                       1

dupe_id     name
----------- --------------------------------------------------
2           Spider man

six_of_one                              half_dozen_of_other                     current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
2                                       2                                       2

dupe_id     name
----------- --------------------------------------------------
Duplicate key was ignored.

this_identity_belongs_to_spider_man     this_identity_also_belongs_to_spider_man current_identity_value
--------------------------------------- ---------------------------------------- ---------------------------------------
2                                       2                                        3

dupe_id     name                                               $action
----------- -------------------------------------------------- ----------
4           Hal Jordan                                         INSERT

hal_jordan                              still_hal_jordan                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
4                                       4                                       4

dupe_id     name
----------- --------------------------------------------------
5           Tony Stark

tony_stark                              still_tony_stark                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
5                                       5                                       5

dupe_id     name                                               $action
----------- -------------------------------------------------- ----------
4           Hal Jordan                                         UPDATE

tony_stark                              still_tony_stark                        current_identity_value
--------------------------------------- --------------------------------------- ---------------------------------------
5                                       5                                       5

Duplicate key was ignored.
dupe_id     name
----------- --------------------------------------------------
7           Barry Allen
1           Peter Parker

dupe_id     name
----------- --------------------------------------------------
0           Peter Parker
0           Barry Allen

Rows in a partitioned table

2011-08-25T08:00:00.022-05:00

I'm punting this query out here so I can reference it from work. For the current project, we are evaluating whether partitioning tables will help us (as well as using multiple data files). One of the warnings I've come across for partitioning is to make sure your partition function is doing what you think it should for a given value. Sounds bloody stupid, I mean how can you bone that? Except of course, I did first time out the gate.

This query ties together a variety of system tables to help me determine if my partition function is working as expected and assigning data into the correct file groups. If there are no partitions, this query won't return anything.


-- This ugly as sin query helps determine how

-- many rows are in tables that use 

-- partitioning

SELECT

    SCHEMA_NAME(T.schema_id) AS [schema_name]

,   T.name AS table_name

,   FDS.name AS file_group_name

,   SP.partition_number

,   PS.name AS partition_scheme

,   PF.name AS partition_function

,   SP.rows AS row_in_partition



FROM

    -- identify all of the tables

    sys.tables T 

    -- Indexes provide data_space_id which is

    -- critial to navigating everything else    

    INNER JOIN 

        sys.indexes SI 

        ON SI.object_id = T.object_id

    INNER JOIN

        sys.partitions SP

        ON SP.index_id = SI.index_id

        AND SP.object_id = T.object_id



    -- Data spaces provides the partition scheme

    INNER JOIN 

        sys.data_spaces DS 

        ON DS.data_space_id = SI.data_space_id

    -- Partition Scheme allows for linking to Partition function

    INNER JOIN

        sys.partition_schemes PS

        ON PS.data_space_id = DS.data_space_id

    INNER JOIN

        sys.partition_functions PF

        ON PF.function_id = PS.function_id

    INNER JOIN

        sys.partition_parameters PP

        ON PP.function_id = PF.function_id



    -- destination data spaces provides the translation between

    -- the partition scheme and the associated file groups

    -- also shows where the those file groups fall w.r.t. partition

    INNER JOIN

        sys.destination_data_spaces DDS

        ON DDS.partition_scheme_id = DS.data_space_id

        AND DDS.destination_id = SP.partition_number

    -- Back to data spaces to identify the names of the

    -- file group name

    INNER JOIN

        sys.data_spaces FDS

        ON FDS.data_space_id = DDS.data_space_id

    -- file groups to retrieve the name of the groups

    -- in case data files were not in line with fg

    INNER JOIN

        sys.filegroups FG

        ON FG.data_space_id = DDS.data_space_id

--inner join

--    sys.partition_functions PF

--    ON PF.function_id = DS.data_space_id

--inner join

--    sys.database_files DF

--    ON DF.file_guid = FG.filegroup_guid

ORDER BY

    SCHEMA_NAME(T.schema_id) 

,   T.name 

,   SP.partition_number

For my example, I am splitting data from a table based on fiscal year and for the demo set, I have an equal distribution of year values from 2008 to 2011. Yes, the names indicate the 2008 data is stored in the 2009 file groups and so on. See note above about verifying you are doing it correctly ;)

schema_name	table_name	file_group_name	partition_number	partition_scheme	partition_function	row_in_partition
dbo	PARTITIONED_DATA	FY_2008	1	FiscalYearPS	FiscalYearPF	0
dbo	PARTITIONED_DATA	FY_2009	2	FiscalYearPS	FiscalYearPF	53
dbo	PARTITIONED_DATA	FY_2010	3	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2011	4	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2012	5	FiscalYearPS	FiscalYearPF	54
dbo	PARTITIONED_DATA	FY_2013	6	FiscalYearPS	FiscalYearPF	0

TSQL: Find the last X in a string

2011-08-16T08:00:00.019-05:00

A question came up about how do you find the last word in a sentence in SQL. My mind immediately went to the clever solution I used far too many times. The last instance of something becomes the first if you approach from the other end. Given the weaksauce string capabilities of TSQL, my solution was to reverse the string, find the first instance of whatever I was looking for and then slice that many characters off the end of the string. It was a good solution, it worked and it only took 2 executions to get it right as I typically forgot to back off one to get the correct starting position. I then saw someone using the MySQL method and they passed in a value of -1 to their find. I then began a feverish prayer of "that doesn't work in SQL Server, that doesn't work in SQL Server." You can run the script for yourself to see whether charindex works with negative values.


DECLARE 

    @longString varchar(300)

,   @control varchar(10)

,   @test varchar(10)

,   @lastSpacePosition int

,   @negativeIndex int



SELECT

    @longString = 'Four score and seven years ago our fathers brought forth on this continent a new nation, conceived in liberty, and dedicated to the proposition that all men are created equal'

,   @control = 'equal'    



-- Find the first space of the reversed string, assumes no trailing space

-- and back off one

SELECT 

    @lastSpacePosition = CHARINDEX(' ', REVERSE(@longString),1) -1



-- Slice off the right N characters

SELECT 

    @test = RIGHT(@longString, @lastSpacePosition)

    

IF (@test = @control)

BEGIN

    PRINT '@billinkc is a genius! Mega-Upvote'

END

ELSE

BEGIN

    PRINT 'Ack! Something went horribly wrong'

END



SELECT 

    @negativeIndex = CHARINDEX(' ', @longString, -1)

    -- nothing up my sleeves

,   @test = NULL



-- Slice off the right N characters

SELECT 

    @test = RIGHT(@longString, @lastSpacePosition)



IF (@test = @control)

BEGIN

    PRINT '@billinkc is a actually a tickturd'

END

ELSE

BEGIN

    PRINT 'perhaps @billinkc is not that bad'

END

SSIS package query

2011-08-15T08:00:00.007-05:00

I like to think of this query as "what the hell do I have deployed where?" It uses a recursive CTE to build out information from the system tables on the SSIS packages deployed to the MSDB and any folder(s) they may be in.

SQL Server 2008/2008 R2


;

WITH FOLDERS AS

(

    -- Capture root node

    SELECT

        cast(PF.foldername AS varchar(max)) AS FolderPath

    ,   PF.folderid

    ,   PF.parentfolderid

    ,   PF.foldername

    FROM

        msdb.dbo.sysssispackagefolders PF

    WHERE

        PF.parentfolderid IS NULL



    -- build recursive hierarchy

    UNION ALL

    SELECT

        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath

    ,   PF.folderid

    ,   PF.parentfolderid

    ,   PF.foldername

    FROM

        msdb.dbo.sysssispackagefolders PF

        INNER JOIN

            FOLDERS F

            ON F.folderid = PF.parentfolderid

)

,   PACKAGES AS

(

    -- pull information about stored SSIS packages

    SELECT

        P.name AS PackageName

    ,   P.id AS PackageId

    ,   P.description as PackageDescription

    ,   P.folderid

    ,   P.packageFormat

    ,   P.packageType

    ,   P.vermajor

    ,   P.verminor

    ,   P.verbuild

    ,   suser_sname(P.ownersid) AS ownername

    FROM

        msdb.dbo.sysssispackages P

)

SELECT 

    F.FolderPath

,   P.PackageName

,   F.FolderPath + '\' + P.PackageName AS PackagePath

,   P.packageFormat

,   P.packageType

,   P.vermajor

,   P.verminor

,   P.verbuild

,   P.ownername

,   P.PackageId

FROM 

    FOLDERS F

    INNER JOIN

        PACKAGES P

        ON P.folderid = F.folderid

-- uncomment this if you want to filter out the 

-- native Data Collector packages

-- WHERE

--     F.FolderPath <> '\Data Collector'

PowerShell SSIS deployment and maintenance

2011-08-11T08:00:00.003-05:00

More than a year ago, I wrote my little post on deploying an SSISManifest file via a PowerShell script which in turn repeatedly invoked dtutil. That was fine and dandy but I've grown since then, taken a new job and discovered that they didn't install all of SQL Server when they created developer machines. We have Visual Studio Team Foundation Server Database Edition aka Data Dude and we have BIDS and the SQL Server client tools installed but there is no joy in Mudville when I attempt to use any of the tools outside of Visual Studio (dtutil, dtexec, dtsinstall).

Invariably, whatever command I attempt to execute will result in an error message stating I don't have integration services installed. dtutil.exe -> "The SQL Server SSIS Package Utilities require Integration Services to be installed by one of these editions of SQL Server 2008 R2: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services." dtsinstall.exe -> "ERROR: The Package Installation Wizard requires Integration Services to be installed by one of these editions..." dtexec.exe -> "To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher." I'm sure from a licensing perspective, this post probably violates the spirit but I'm going to sidestep that for now and assume you, the awesome reader (Hi Mom!), is interested in this solution so that you can have more power over your SSIS installations.

I've learned quite a bit of PowerShell syntax over the past 2 months in my new job. It's nothing I had to do, wasn't even a bullet point on their required skills but I've been needing to glue some things together and PowerShell seemed the perfect tool. As a developer, I really see PowerShell as a scriptable .NET language. It's decent for prototyping and shouldn't raise hackles like "here's an exe, just run it on the server, it's fine"

PowerShell SSIS deployment and maintenance

This script builds on the concepts of my earlier script but ditches wrapping the dtutil method in lieu of directly using the object model. Plus, it feels like the more correct solution. Save this script as something like C:\src\DeployManifest.ps1


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | out-null



# This method will create a folder on SQL Server for SSIS package deployment

Function Create-FolderOnSqlServer

{

    param

    (

        [string]$Server,

        [string]$Folder

    )

    

    $folderDelimter = "\"

    

    try

    {

        # if they supply multiple-level of folders, might need to create whole structure

        # while recursion would be a more elegant solution, my mind is not there

        if ($Folder -ne $folderDelimter)

        {

            $folderList = $Folder.Split($folderDelimter)

        }

        else

        {

            $folderList = [array]$Folder

        }

        

        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application

        $parentFolder = "\"



        foreach($subFolder in $folderList)

        {

            # test for existing folders

            Write-Debug ([System.string]::Format("parentFolder:{0} folderDelimter:{1} subFolder:{2}", $parentFolder, $folderDelimter, $subFolder))

            $testFolder = $parentFolder + $folderDelimter + $subFolder

            if (!$app.FolderExistsOnSqlServer($testFolder, $Server, $null, $null))

            {

                Write-Debug ([System.string]::Format("Folder {0} does not exist on server, creating", $testFolder))

                $app.CreateFolderOnSqlServer($parentFolder, $subFolder, $Server, $null, $null);

                Write-Host ([System.string]::Format("Created folder {0} on server {1}", $testFolder, $Server))

            }

            else

            {

                Write-Debug "Folder exists on server, doing nothing"

            }

            

            $parentFolder += $folderDelimter + $subFolder

        }

    }

    catch 

    {

        Write-Error ([string]::Format("Failed to create folder {0} on server {1}", $folder, $server))

        Write-Error $_ | fl * -Force

    }

}



# Deploy a package to a folder

Function Copy-Package

{

    param

    (

        [string]$Server,

        [string]$PathToDtsx,

        [string]$ProjectFolder = "\\"

    )

    

    $events = $null

    $userName = $null

    $password = $null

    

    Write-Debug "--------------------------Copy-Package----------------------"

    Write-Debug ([string]::Format("File: {0}", $PathToDtsx))

    Write-Debug ([string]::Format("Server: {0}", $Server))

    Write-Debug ([string]::Format("Folder: {0}", $ProjectFolder))



    if ($ProjectFolder -ne "\\")

    {

        Create-FolderOnSqlServer $Server $ProjectFolder

    }



    Write-Debug ([string]::Format("Attempting to deploy {0} to server {1}", $PathToDtsx, $Server))

    

    try

    {

        $app = New-Object Microsoft.SqlServer.Dts.Runtime.Application

        $package = $app.LoadPackage($PathToDtsx, $null)

        

        $destinationName = "\\"

        if ($ProjectFolder -ne "\\")

        {

            $destinationName = $destinationName + $ProjectFolder + "__"

        }



        # \\folder\\packageName

        $destinationName = $destinationName + $package.Name

        Write-Debug ([string]::Format("Saving as {0}", $destinationName))

        $app.SaveToSqlServerAs($package, $events, $destinationName, $Server, $userName, $password);

        Write-Host ([string]::Format("Deployed {0}", $destinationName))

    }

    catch 

    {

        Write-Error ([string]::Format("Failed to deploy {0} to server {1}", $PathToDtsx, $Server))

        Write-Error $_ | fl * -Force

    }

}



# This method cracks open an SSIS manifest file and deploys all packages to

# the indicated server

Function Deploy-Manifest

{

    param

    (

        [string]$Server,

        [string]$ManifestFile,

        [string]$ProjectFolder = "\\"

    )

    # This deploy script assumes all packages are in the same folder as the 

    # manifest



    $baseFolder = [System.IO.Path]::GetDirectoryName($ManifestFile)



    [xml] $list = Get-Content $ManifestFile



    Write-Debug "--------------------------Deploy-Manifest----------------------"

    Write-Debug ([string]::Format("    Server {0}", $Server))

    Write-Debug ([string]::Format("    ProjectFolder {0}", $ProjectFolder))

    foreach($package in $list.DTSDeploymentManifest.Package)

    {

        $basePackage = [System.IO.Path]::GetFileNameWithoutExtension($package)

        

        # This might need to be a relative path

        $fullyQualifiedPackage = [System.IO.Path]::Combine($baseFolder, $package)

        Write-Debug ([string]::Format("    Package {0}", $fullyQualifiedPackage))

    

        Copy-Package $Server $fullyQualifiedPackage $ProjectFolder

    }

}



# this script is reponsible for enumerating subfolder

# for each manifest it finds, it will invoke the deploy method on it

Function Walk-SubFolder

{

    param

    (

        [string]$RootFolder,

        [string]$DeployServer

    )

    

    $searchPattern = "*.ssisdeploymentmanifest"

    

    foreach($manifestFile in [System.IO.Directory]::GetFiles($RootFolder, $searchPattern, [System.IO.SearchOption]::AllDirectories))

    {

        [xml] $list = Get-Content $manifestFile

        # hard coding the root folder DBServer

        $deployFolder = ([string]::Format("Default\{0}", $list.DTSDeploymentManifest.GeneratedFromProjectName))

        Write-Host ([string]::Format("Deploying manifest file {0}", $manifestFile))

        Deploy-Manifest $DeployServer $manifestFile $deployFolder

    }

}

What I like about this approach is that it let's me get my job done, it allows for unattended installs of SSIS and it handles deploys to subfolders. It still has warts though, I think it's fairly obvious I've not spent a great deal of time polishing it. I'm still learning PowerShell so I was trying to escape backslashes early on but they don't seem to matter, or at least the assembly's methods don't seem to mind doubled and singled backslashes. The error handling is something I've scrounged from the interblags so I don't know if it's the best approach or not.

Usage

The purpose of DeployManifest.PS1 is to crawl all the subfolders from a given root, looking for anything that has an .ssisDeploymentManifest file. For every one of those it finds, it will rip the XML (manifest) apart and extract the project name and the list of packages. It will create a nested folder on the server (Default\ProjectName) and then deploy all the packages Assumptions:

Deployments will go to Default\ProjectName
The ssisDeploymentManifest will be in the same folder as the dtsx packages it lists
You have run this command in PowerShell: Set-ExecutionPolicy Remote-Signed


C:\Src

      \ModuleA\Solution1\ProjectFoo\

                                    ProjectFoo.ssisDeploymentManifest

                                    Package1.dtsx

                                    Package2.dtsx

      \ModuleB\Solution2\Sales\

                               Sales.ssisDeploymentManifest

                               SalesComputeCommission.dtsx

                               SalesGenerateManagerSummaries.dtsx

              \Staging\Staging\

                               Staging.ssisDeploymentManifest

                               StagingLoadFiles.dtsx

Given a structure like the above, we could chose to deploy everything or just a specific project based on the starting folder (Src, ModuleA, Staging). Create a second PowerShell script, this one will actually use the library we built above and saved to C:\Src\DeployManifest.ps1 This script is handy in that it can be dropped at any location in that Src tree and it will deploy everything below it. Simply save the following script as DeployPackages.ps1 and try invoking it from different locations. If it's in \Src, it should find 3 manifests, create 4 folders (Default + 3 project subfolders) and serialize 5 packages into msdb (msdb.dbo.sysssispackages or msdb.dbo.sysdtspackages90 depending on your SQL Server version)


# This script attempts to deploy everything below this folder that has an SSIS manifest file



# This is the "dot include" mechanism for PowerShell which is 

# the PowerShell way of re-using an existing script

. "C:\Src\DeployManifest.ps1"



$deployServer = "localhost\DEV2008R2"

$baseFolder = ".\"

Walk-SubFolder $baseFolder $deployServer

What's left? I have the basics of package delete and a recursive folder delete but I'm reworking it to make it more PowerShell-onic (function names and parameters don't meet conventions etc)

T-SQL Tuesday #21 - A Day Late and Totally Full of It

2011-08-10T08:00:00.035-05:00

Reveal your crap to the world. Why is (or was) it crap? Why did you do it? And how did you learn from your mistake?

All right Mr. De Mille, I'm ready for my close-up

While I have plenty of crap code, heck, got some lined up for tomorrow and hard lessons learned from it, I thought I'd actually talk about a soft topic. In my first three jobs out of college, I made a terrible assumption that I would be rewarded for doing my job, being a loyal employee, etc. I had this idyllic 50's era notion of being a good employee would result in my happiness. In my mind, I'd bust my ass working for someone and they'd reward me with money, a pat on the head and the opportunity to solve more complex problems. The reality was I'd bust my hump and get an email with termination notice in it (the owner kept telling clients to go F' themselves so they wouldn't do business with us---go figure) or I'd go into an annual review and receive feedback of "this is designed to hurt" as ~~redacted~~ or be told I can suck it up because I should have negotiated some vacation time or a better salary before I started the job.

Crap, crap, megacrap

Why was that crap? I was putting someone else in charge of my happiness and job satisfaction. I assumed they knew better or at least, I wanted to believe they did. I suspect the why is that I didn't want to take responsibility for my own life. I'm a mellow, generally non-confrontational type of person and asking for what I want is nothing I ever really learned how to do. Plus, it was easier being unhappy than doing something about it. Change is hard and scary.

wat do?

I learned that the only one responsible for my happiness is me. I'm blessed in that my career choice was correct, I love computers and programming, just that I needed to do something that made my heart happy.

I eventually took charge of my career and got myself out of my comfort zone. It's the only way to grow.
I gave up on the idea of my company taking care of my needs, even if I voiced them precisely.
I figured out what I didn't want to do (ASP all the time, 4GL languages, VB, maintain other people's code, put out fires all day long, Access), which is similar to figuring out what exactly I want to do (SQL Server stuff).
I made time to attend user group meetings.
I got involved with other database professionals. Twitter in particular has been a fantastic conduit for building relationships with others in my trade.
I bought a developer edition of SQL Server 2008 for a whopping $37.
I found a boss that supported me and sent me to 2009 Summit which changed my life as I caught a glimpse of what I wanted to be---good enough with SQL Server to present there.
I bought an MSDN license and have been randomly exploring all that Microsoft has to offer
I've organized a SQL Saturday and am working on the next one.
I created a training plan and am working on certifications