Monthly Archives: July 2011

Launch DtExec from PowerShell

Posted by timvw on July 15, 2011 None comments

Running an SSIS package from PowerShell (using DTExec) can be as simple as:

RunPackage -File 'C:\test.dtsx' -DatabaseHost '.' -DatabaseName 'TEST';

Here are the functions that make it this simple:

function GetDtExecPath {
    $DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
    $DtExecPath = (Resolve-Path "$DtsPath\Binn\DTExec.exe");
    $DtExecPath;
}

function GetDtExecPropertyPathValue() {
    param(
        $PropertyPath = '',
        $Value = ''
    );
    "$PropertyPath;\`"`"$Value\`"`"";
}

function RunPackage {
    param(
        $DtExecPath = (GetDtExecPath),
        $File = 'test.dtsx'
    );
    
    $Params = "/FILE $File";
    for($i = 0; $i -lt $Args.Length; $i += 2) {
        $PropertyPath = $Args[$i].SubString(1);
        $Value = $Args[$i+1];
        $PropertyPathValue = GetDtExecPropertyPathValue -PropertyPath $PropertyPath -Value $Value;
        $Params += " /SET $PropertyPathValue";
    } 
    
    &"$DtExecPath" $Params;
}

Building a Nums table (quickly)

Posted by timvw on July 13, 2011 None comments

A while ago i presented my approach to generate a nums table here. 

DECLARE @count INT = 1000;
 
WITH
    [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
  , [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
  , [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
  , [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
  , [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY [Value]) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];

Because we only use this code once to fill the table we don’t really care that it is not very fast. Today i discovered there is a way to speed it up in this wonderful book: Inside Microsoft® SQL Server® 2008: T-SQL Querying:

WITH
    [Nums1] AS ( SELECT 1 AS [Value] UNION SELECT 2 AS [Value] )
  , [Nums2] AS ( SELECT A.* FROM [Nums1] AS A, [Nums1] AS B, [Nums1] AS C)
  , [Nums3] AS ( SELECT A.* FROM [Nums2] AS A, [Nums2] AS B, [Nums2] AS C)
  , [Nums4] AS ( SELECT A.* FROM [Nums3] AS A, [Nums3] AS B )
  , [Numbers] AS ( SELECT TOP(@count) -1 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS [Value] FROM[Nums4] )
SELECT * FROM [Numbers];

Yay for features like ORDER BY (SELECT <Constant>).

Using User-Defined Table Type with Identity column in ADO.NET

Posted by timvw on July 13, 2011 None comments

A while ago i wanted to use a User-Defined Table Type to pass in a set of records. Nothing special about this except that the first column of the UDTT was an Identity column:

CREATE TYPE [Star].[example] AS TABLE(
  [Ordinal] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
)

After finding a lot of posts saying that this is not supported a colleague of mine, Stephen Horsfield, found a way to do it as following:

var sqlMetaData = new[] 
{
  new SqlMetaData("Ordinal", SqlDbType.Int, true, false, SortOrder.Unspecified, -1),
  new SqlMetaData("Name", SqlDbType.NVarChar, 200)
};

sqlRecords = new HashSet<SqlDataRecord>(usersToInclude.Select(user =>
{
  var record = new SqlDataRecord(sqlMetaData);
  record.SetString(1, user.Name);
  return record;
}));