Looking for references in SSIS

Similar to my post of a few years ago where you may want to search an SSRS package (Querying Information from Deployed SSRS Packages) you can also search SSIS. The big differences are the namespaces used and that sometimes the package isn’t actually in a database to be queried. This example should show at least one way to get around each problem.

-- This tells you exactly where in a package a query containing the search criteria (@F) can be found. 

-- Be careful in your specification of table/column names as wildcards and patterns are not allowed 

-- and some references are generated as [schema].[name] while others are just schema.name.

Declare @F varchar(100) = 'TextToFind';
 
With XMLNAMESPACES(N'www.microsoft.com/SqlServer/Dts'               AS DTS
                  ,N'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask
                  ,N'http://www.w3.org/2001/XMLSchema'              AS xsd
                  ,N'urn:schemas-microsoft-com:xml-msdata'          AS msdata)
,   FileData AS (select cast( [SSISPkgData].BulkColumn AS XML) AS pkgXML
                   from openrowset(bulk 'H:\SomePackage.dtsx',single_blob) as [SSISPkgData])
, Found as (                                 
select Nodes.value('@SQLTask:SqlStatementSource','varchar(MAX)') Txt
      -- Notice this very cool feature.  From SSMS results window you can click on the statement to see it. 

      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('@SQLTask:SqlStatementSource'
                    ,'varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//SQLTask:SqlTaskData[contains(upper-case(@SQLTask:SqlStatementSource)

                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(..)','varchar(max)') Parent
      ,Nodes.value('../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../..)','varchar(max)') GrandParent
      ,Nodes.value('../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//DTS:Property[@DTS:Name="Expression" and . != "" and contains(upper-case(.)

                            ,upper-case(sql:variable("@F")))]') Pkg(Nodes)
union all
select Nodes.value('.','varchar(MAX)') Txt
      ,QueryText  =(select [processing-instruction(q)]=Nodes.value('.','varchar(MAX)') for xml path(''),type)
      ,Nodes.value('local-name(../..)','varchar(max)') Parent
      ,Nodes.value('../../@name','varchar(MAX)') ParentName
      ,Nodes.value('local-name(../../../../../..)','varchar(max)') GrandParent
      ,Nodes.value('../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GrandParentName
      ,Nodes.value('local-name(../../../../../../..)','varchar(max)') GGParent
      ,Nodes.value('../../../../../../../DTS:Property[@DTS:Name="ObjectName"][1]','varchar(MAX)') GGParentName
  from FileData F
 CROSS APPLY F.pkgXML.nodes('//property[(@name="SqlCommand" or @name="SqlCommandVariable" or 

                             @name="OpenRowset" or @name="OpenRowsetVariable") and . != "" and 

                             contains(upper-case(.),upper-case(sql:variable("@F")))]') Pkg(Nodes)
)
Select Parent,ParentName,GrandParent,GrandParentName,GGParent,GGParentName,QueryText
  from Found
 --where Txt like '%update%'  -- use this to further restrict results

T-SQL date formulas

There is a PowerShell version that does more here:
http://rickbielawski.wordpress.com/2009/03/13/adding-lots-of-useful-datetime-functions/
This shows how easy it is to do in T-SQL.

-- =============================================
-- Author:      Rick Bielawski
-- Create date: 2008/05/13
-- Description: Truncates a date/time to a particular date-part.
--              NOTE - THIS IS TRUNCATION, NOT DATEPART !
--              For example:
--              select dbo.Trunc_DateTime('12/31/1999', 1) --is 01/01/1999 (year)
--              select dbo.Trunc_DateTime('12/31/1999', 2) --is 12/01/1999 (month)
--              select dbo.Trunc_DateTime('12/31/1999',12) --is 10/01/1999 (quarter)
-- @DateTime    is the value to be truncated.
-- @Part        is the part of the date to truncate to.
--              1 - year
--              2 - month
--              3 - day
--              4 - hour
--              5 - minute
--              6 - second
--           Parts of a year
--             10 - Same as 1 - Start of year
--             11 - Half (Jan,Jul)
--             12 - Quarter (Jan,Apr,Jul,Oct)
--           Parts of a month
--             20 - Same as 2 - First day of month
--           Parts of a day
--             30 - (Same as 3)
--             31 - Half day Midnight or Noon (12 hr period)
--             32 - Third of a day Midnight, 8:am, 4:pm
--           Parts of an hour
--             40 - (Same as 4)
--             41 - Half hour (:00 or :30 minute)
--             42 - Third hour (:00, :20, :40)
--             43 - Quarter hour (:00, :15, :30, :45)
--             44 - 10 minutes (:00, :10, :20, ...)
--           Parts of a week (most recent X day of week)
--             71 - Sunday
--             72 - Monday
--             73 - Tuesday
--             74 - Wednesday
--             75 - Thursday
--             76 - Friday
--             77 - Saturday
--           Versionable truncations
--            100 - Business day (NOT IMPLEMENTED)
-- =============================================
CREATE FUNCTION [dbo].[Trunc_DateTime]
    (@DateTime datetime, @Part int)
RETURNS datetime
AS
BEGIN
  -- The @base date 19950101 was selected as base because it is a Sunday
  -- and therefore makes computations based on day-of-week simpler.
  -- Calculations using the dateadd & datediff 'week' param only work
  -- correctly when the @base date is on the same day of the week as
  -- DATEFIRST which defaults to Sunday.  There are similar dependencies
  -- when using year or month.  Thus a year that starts on Sunday is apropos.
  Declare @base datetime;
  Set     @base = '19950101'
    RETURN CASE @Part
-- Basic parts of a date (year,month,day,hour,minute,second)
        WHEN 1  THEN DATEADD(year,    DATEDIFF(year,    @base,   @DateTime),        @base)
        WHEN 2  THEN DATEADD(month,   DATEDIFF(month,   @base,   @DateTime),        @base)
        WHEN 3  THEN DATEADD(day,     DATEDIFF(day,     @base,   @DateTime),        @base)
        WHEN 4  THEN DATEADD(hour,    DATEDIFF(hour,    @base,   @DateTime),        @base)
        WHEN 5  THEN DATEADD(minute,  DATEDIFF(minute,  @base,   @DateTime),        @base)
        WHEN 6  THEN DATEADD(second,  DATEDIFF(second,  @base,   @DateTime),        @base)
-- Parts of a year (yr-start,half, quarter)
        WHEN 10 THEN DATEADD(year,    DATEDIFF(year,    @base,   @DateTime),        @base)
        WHEN 11 THEN DATEADD(month,  (DATEDIFF(month,   @base,   @datetime)/ 6)* 6, @base)
        WHEN 12 THEN DATEADD(quarter, DATEDIFF(quarter, @base,   @DateTime),        @base)
-- Parts of a month (month start)
        WHEN 20 THEN DATEADD(month,   DATEDIFF(month,   @base,   @DateTime),        @base)
-- Parts of a day (start of day, half (noon or midnight), third(0,8am,4pm))
        WHEN 30 THEN DATEADD(day,     DATEDIFF(day,     @base,   @DateTime),        @base)
        WHEN 31 THEN DATEADD(hour,   (DATEDIFF(hour,    @base,   @DateTime)/12)*12, @base)
        WHEN 32 THEN DATEADD(hour,   (DATEDIFF(hour,    @base,   @DateTime)/ 8)* 8, @base)
-- Parts of an hour (current hour, half hr, 1/4 hr, 10 min)
        WHEN 40 THEN DATEADD(hour,    DATEDIFF(hour,    @base,   @DateTime),        @base)
        WHEN 41 THEN DATEADD(minute, (DATEDIFF(minute,  @base,   @DateTime)/30)*30, @base)
        WHEN 42 THEN DATEADD(minute, (DATEDIFF(minute,  @base,   @DateTime)/20)*20, @base)
        WHEN 43 THEN DATEADD(minute, (DATEDIFF(minute,  @base,   @DateTime)/15)*15, @base)
        WHEN 44 THEN DATEADD(minute, (DATEDIFF(minute,  @base,   @DateTime)/10)*10, @base)
-- Parts of a week (Week,Sun,Mon,Tues,Wed,Thu,Fri,Sat)
        WHEN 71 THEN DATEADD(day,    (DATEDIFF(day,     @base,   @datetime)/ 7)* 7, @base  )
        WHEN 72 THEN DATEADD(day,    (DATEDIFF(day,     @base+1, @datetime)/ 7)* 7, @base+1)
        WHEN 73 THEN DATEADD(day,    (DATEDIFF(day,     @base+2, @datetime)/ 7)* 7, @base+2)
        WHEN 74 THEN DATEADD(day,    (DATEDIFF(day,     @base+3, @datetime)/ 7)* 7, @base+3)
        WHEN 75 THEN DATEADD(day,    (DATEDIFF(day,     @base+4, @datetime)/ 7)* 7, @base+4)
        WHEN 76 THEN DATEADD(day,    (DATEDIFF(day,     @base+5, @datetime)/ 7)* 7, @base+5)
        WHEN 77 THEN DATEADD(day,    (DATEDIFF(day,     @base+6, @datetime)/ 7)* 7, @base+6)
-- Return same value as that passed if invalid conversion value is passed
                ELSE @DateTime
       END;
END
GO
-----------------------------------------------------------------
declare @DateTime datetime
set @DateTime = '2009/11/16 22:47:31.562'
--set @DateTime = '2009/05/16 10:27:31.562'
select  0,dbo.Trunc_DateTime(@DateTime, 0),'Test-Date  ' union all
select  1,dbo.Trunc_DateTime(@DateTime, 1),'Year       ' union all
select  2,dbo.Trunc_DateTime(@DateTime, 2),'Month      ' union all
select  3,dbo.Trunc_DateTime(@DateTime, 3),'Day        ' union all
select  4,dbo.Trunc_DateTime(@DateTime, 4),'Hour       ' union all
select  5,dbo.Trunc_DateTime(@DateTime, 5),'Minute     ' union all
select  6,dbo.Trunc_DateTime(@DateTime, 6),'Second     ' union all
select 10,dbo.Trunc_DateTime(@DateTime,10),'Year       ' union all
select 11,dbo.Trunc_DateTime(@DateTime,11),'1/2 yr     ' union all
select 12,dbo.Trunc_DateTime(@DateTime,12),'1/4 yr     ' union all
select 20,dbo.Trunc_DateTime(@DateTime,20),'Month      ' union all
select 30,dbo.Trunc_DateTime(@DateTime,30),'Day        ' union all
select 31,dbo.Trunc_DateTime(@DateTime,31),'1/2 day  12' union all
select 32,dbo.Trunc_DateTime(@DateTime,32),'1/3 day   8' union all
select 40,dbo.Trunc_DateTime(@DateTime,40),'Hour       ' union all
select 41,dbo.Trunc_DateTime(@DateTime,41),'1/2 hr   30' union all
select 42,dbo.Trunc_DateTime(@DateTime,42),'1/3 hr   20' union all
select 43,dbo.Trunc_DateTime(@DateTime,43),'1/4 hr   15' union all
select 44,dbo.Trunc_DateTime(@DateTime,44),'1/6 hr   10' union all
select 71,dbo.Trunc_DateTime(@DateTime,71),'Sunday     ' union all
select 72,dbo.Trunc_DateTime(@DateTime,72),'Monday     ' union all
select 73,dbo.Trunc_DateTime(@DateTime,73),'Tuesday    ' union all
select 74,dbo.Trunc_DateTime(@DateTime,74),'Wednesday  ' union all
select 75,dbo.Trunc_DateTime(@DateTime,75),'Thursday   ' union all
select 76,dbo.Trunc_DateTime(@DateTime,76),'Friday     ' union all
select 77,dbo.Trunc_DateTime(@DateTime,77),'Saturday   ' union all
select  7,dbo.Trunc_DateTime(@DateTime, 7),'Test-Date  '
-----------------------------------------------------------------
GO

Quick function for producing numbers

You may want to avoid the space and un-cached I/O involved in using a numbers table. This produces exactly the right number of numbers quick and easy. Really! Benchmark it. I got 1M rows in 0.26sec on my laptop.

Note that there is a limit to the number of rows this can return.
But with a clean install of 2008 (no tables defined yet) I can generate over 25M.
By adding another param and a simple addition to the function’s query you can return any range.  For example “select n from Numbers(-32768, 32767)”.

-- Returns numbers from 0 up to (but not including) @COUNT 
-- A decent replacement for master.dbo.spt_values when you need more than 0-2047 
CREATE FUNCTION Numbers (@COUNT int) RETURNS TABLE AS RETURN ( 
SELECT TOP (SELECT @COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1 AS N 
FROM master.sys.all_columns A 
CROSS APPLY master.sys.all_columns B )

XSLT to transform Visual Studio Documentation to HTML

This isn’t really the greatest script but it does a fair job and makes a great starting point if you want to roll your own.

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <!--                Created by Rick Bielawski - published 2011-02-18                   -->
  <!--                             last updated 2011-06-05                               -->
  <!--  To display an XML documentation file generated by Visual Studio with a browser   -->
  <!--  add the line below immediately after the ?xml version line in your XML document  -->
  <!--  then open the XML file with your browser. Obviously it must point to THIS file.  -->
  <!--        <?xml-stylesheet href="visual-studio-xml-doc.xsl" type="text/xsl"?>        -->
  <!--  Or, use an XSLT generator with this code on your XML document to create html     -->
 
  <!-- ======================================================================
       Here we declare ancilary routines used to extract data
       ====================================================================== -->
  <!--  Replace a given string with another.  Basically this is only needed because long
        parameter lists don't wrap at a comma in HTML without a space being inserted.  -->
  <xsl:template name="tReplaceSubString">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="','" />
    <xsl:param name="pNewValue" select="' ,'" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:value-of select="substring-before($pText,$pLookFor)" />
        <xsl:value-of select="$pNewValue" />
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor"  select="$pLookFor" />
          <xsl:with-param name="pNewValue" select="$pNewValue" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pText" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Counting the number of levels in a member's name allows me to tell
        where the name of the entity being documented starts (after the last one).  -->
  <xsl:template name="tCountOccurances">
    <xsl:param name="pText" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pCount" select="0" />
    <xsl:choose>
      <xsl:when test="contains($pText,$pLookFor)">
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText"    select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pLookFor" select="$pLookFor" />
          <xsl:with-param name="pCount"   select="$pCount + 1" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$pCount" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!--  Replaces the pCount instance of pLookFor with pSeparator.  Used to split a string.
        pCount=0 doesn't look for the pLookFor.  It just puts pSeperator at the beginning.
        pCount=1 splits at the 1st occurance, 2 splits at the 2nd occurance....
        Splitting on a count greater than the number of occurances puts pSeperator at the end.  -->
  <xsl:template name="tSplitAtInstance">
    <xsl:param name="pText" />
    <xsl:param name="pCount" select="0" />
    <xsl:param name="pLookFor" select="'.'" />
    <xsl:param name="pSeparator" select="'|'" />
    <xsl:param name="pStartEmpty" />
    <xsl:choose>
      <xsl:when test="$pCount &gt; 0 and contains($pText,$pLookFor)">
        <xsl:variable name="temp">
          <xsl:if test="$pStartEmpty = ''">
            <xsl:value-of select="substring-before($pText,$pLookFor)" />
          </xsl:if>
          <xsl:if test="$pStartEmpty != ''">
            <xsl:value-of select="concat($pStartEmpty,$pLookFor,substring-before($pText,$pLookFor))" />
          </xsl:if>
        </xsl:variable>
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"       select="substring-after($pText,$pLookFor)" />
          <xsl:with-param name="pCount"      select="$pCount - 1" />
          <xsl:with-param name="pLookFor"    select="$pLookFor" />
          <xsl:with-param name="pSeparator"  select="$pSeparator" />
          <xsl:with-param name="pStartEmpty" select="$temp" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="concat($pStartEmpty,$pSeparator,$pText)" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
 
  <!-- ======================================================================
       These format individual parts of the document.
       ====================================================================== -->
 
  <!--  This seems to be the only thing that can be done generically -->
  <xsl:template name="tElementName">
    <xsl:param name="pText" />
    <span class="ElementName"><xsl:value-of select="$pText" />: </span>
  </xsl:template>
 
  <!-- ======================================================================
 
       This is where actual transformation starts
 
       ====================================================================== -->
 
  <!--  Set a var with the name of the assembly for the documented project. -->
  <xsl:variable name="vAssembly">
    <xsl:value-of select="doc/assembly/name" />
  </xsl:variable>
 
  <!--  This creates the HTML document within which the XML content is formatted. -->
  <xsl:template match="doc">
    <HTML>
      <HEAD>
        <style type="text/css">                 
          h1 {font-weight:bold; text-decoration:underline; text-align:center}
          pre {margin-top:0; margin-bottom:0}
          table {margin-left:5mm; margin-top:0; margin-bottom:0}
          tr#FirstRow {font-size:1.2em; text-decoration:underline}
          td {padding-left:2em}
          
          .AttributeName {font-weight:bold; padding-right:1em; color:blue; font-family:"Monospace"}
          .ClassName {color:red}
          .CodeItem {font-family:"Monospace"}
          .ElementName {padding-right:5px; text-transform:capitalize; font-weight:bold; font-style:italic}
          .EntityName {margin-left:2em; font-weight:bold; font-size:1.2em; font-family:"Monospace"}
          .MemberTypeName {color:blue}
 
          .summary {margin-top:0.3em; margin-bottom:0.3em}
          .returns {margin-top:0.3em; margin-bottom:0.3em}
          .exception {margin-top:0.3em; margin-bottom:0.3em}
          .remarks {margin-top:0.3em; margin-bottom:0.3em}
        </style>
        <TITLE><xsl:value-of select="$vAssembly" /> documentation</TITLE>
      </HEAD>
      <BODY>
        <h1><xsl:value-of select="$vAssembly" /> documentation</h1><p />
        <xsl:apply-templates select="members/member" />
      </BODY>
    </HTML>
  </xsl:template>
 
  <!--  This template first extracts information about the Member being formatted   -->
  <!--  Then formats the individual doc sections: summary, param, return...   -->
  <xsl:template match="member">
 
    <!-- I decided to use the <para> tag to flag the entity as public.  This way I can
         document internal stuff for intellisense but not for users.  It works well
         since intellisense ignores <para> formatting anyway.  -->
    <xsl:if test="count(summary/para) > 0">
 
      <!--  The whole name has the type, namespace, class name, and member name.   -->
      <xsl:variable name="vWholeName">
        <xsl:value-of select="@name" />
      </xsl:variable>
 
      <!--  The type of member being documented is encoded in the first character   -->
      <xsl:variable name="vMemberType">
        <xsl:value-of select="substring-before($vWholeName,':')" />
      </xsl:variable>
      <xsl:variable name="vMemberTypeName">
        <xsl:choose>
          <xsl:when test="$vMemberType = 'E'">Event</xsl:when>
          <xsl:when test="$vMemberType = 'F'">Field</xsl:when>
          <xsl:when test="$vMemberType = 'M'">Method</xsl:when>
          <xsl:when test="$vMemberType = 'N'">NameSpace</xsl:when>
          <xsl:when test="$vMemberType = 'P'">Property</xsl:when>
          <xsl:when test="$vMemberType = 'T'">Type</xsl:when>
          <xsl:otherwise>Unknown</xsl:otherwise>
        </xsl:choose>
      </xsl:variable>
 
      <!--  We need the number of name parts to get the last part (the name of the entity).
            Unfortunately there might be periods in parameter data types so we must count
            only those that come before any open paren.  -->
      <xsl:variable name="vNumPeriods">
        <xsl:variable name="temp">
          <xsl:choose>
            <xsl:when test="contains($vWholeName,'(')">
              <xsl:value-of select="substring-before($vWholeName,'(')" />
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="$vWholeName" />
            </xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:call-template name="tCountOccurances">
          <xsl:with-param name="pText" select="$temp" />
          <xsl:with-param name="pLookFor" select="'.'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Split the full name between entity name (and any args) and the path (ns,class...)  -->
      <xsl:variable name="vSplitName">
        <xsl:call-template name="tSplitAtInstance">
          <xsl:with-param name="pText"      select="$vWholeName" />
          <xsl:with-param name="pCount"     select="$vNumPeriods" />
          <xsl:with-param name="pLookFor"   select="'.'" />
          <xsl:with-param name="pSeparator" select="'|'" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  Get the entity name (and any args).
            Add space to commas so browsers can wrap them if needed  -->
      <xsl:variable name="vEntityName">
        <xsl:call-template name="tReplaceSubString">
          <xsl:with-param name="pText"     select="substring-after($vSplitName,'|')" />
          <xsl:with-param name="pLookFor"  select="','" />
          <xsl:with-param name="pNewValue" select="', '" />
        </xsl:call-template>
      </xsl:variable>
 
      <!--  We'll assume the assembly name is also the prime Namespace name and omit it.
            The 'class' name might also include any sub-namespace names.  -->
      <xsl:variable name="vClass">
        <xsl:value-of select="substring-after(substring-before($vSplitName,'|'),'.')" />
      </xsl:variable>
 
      <!--  Variables are populated.  This formats member data on the page.  -->
      <xsl:if test="not($vClass='')">
        <span class="ClassName">
          <xsl:value-of select="$vClass" /> -
        </span>
      </xsl:if>
      <span class="MemberTypeName">
        <xsl:value-of select="$vMemberTypeName" />
      </span>:
      <div class="EntityName">
        <xsl:value-of select="$vEntityName" />
        <xsl:if test="$vMemberType = 'M' and not(contains($vWholeName,'('))">()</xsl:if>
      </div>
      <!-- And now, report all the sub-elements -->
      <xsl:apply-templates select="summary" />
      <xsl:apply-templates select="param" />
      <xsl:apply-templates select="returns" />
      <xsl:apply-templates select="interface" />
      <xsl:apply-templates select="exception" />
      <xsl:apply-templates select="example" />
      <xsl:apply-templates select="remarks" />
      <hr />
    </xsl:if>
  </xsl:template>
 
  <!-- These format the primary documentation elements -->
 
  <xsl:template match="summary">
    <div class="summary">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="param">
    <div class="param">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="returns">
    <div class="returns">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="interface">
    <div class="interface">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <span class="AttributeName"><xsl:value-of select="@name" /></span>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="exception">
    <div class="exception">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      (<xsl:value-of select="substring-after(@cref,'.')" />)
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
  <xsl:template match="example">
    <div class="example">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <pre><xsl:value-of select="." /></pre>
    </div>
  </xsl:template>
  <xsl:template match="remarks">
    <div class="remarks">
      <xsl:call-template name="tElementName">
        <xsl:with-param name="pText" select="name()" />
      </xsl:call-template>
      <xsl:apply-templates select="node()" />
    </div>
  </xsl:template>
 
  <!-- Document elements can contain custom formatting elements.  -->
 
  <xsl:template match="para">
    <xsl:if test="not(.='')">
      <xsl:apply-templates select="node()" />
      <br />
    </xsl:if>
  </xsl:template>
 
  <xsl:template match="c">
    <span class="CodeItem">
      <xsl:value-of select="." />
    </span>
  </xsl:template>
 
  <xsl:template match="list">
    <table>
      <tr id="FirstRow">
        <td>Item</td>
        <td>Description</td>
      </tr>
      <xsl:apply-templates select="node()" />
    </table>
  </xsl:template>
  <xsl:template match="item">
    <tr><xsl:apply-templates select="node()" /></tr>
  </xsl:template>
  <xsl:template match="term">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
  <xsl:template match="description">
    <td><xsl:value-of select="." /></td>
  </xsl:template>
 
</xsl:stylesheet>

Getting around not having inline scalar functions

It is well-known that Scalar functions (still) perform poorly (as of 2008) when used in selects returning a large number of rows. But there are good reasons for wishing they performed better. It’s often advantageous to encapsulate certain logic but too often it’s not worth the current performance hit.
Below I demonstrate a technique using an inline table function that, while not pretty, does provide encapsulation WITHOUT the performance impact. In this example I encapsulate a formula to display only YYYY-MM from a date field.

 CREATE FUNCTION itf_YYYY_MM(@Date DATETIME) RETURNS TABLE
WITH SCHEMABINDING AS
RETURN SELECT CAST(YEAR(@Date) AS CHAR(4)) + '-'
      + RIGHT('0' + CAST(MONTH(@Date) AS VARCHAR(2)),2) AS [YYYY-MM];  

Unfortunately this can’t simply be used like a scalar function. IOW this does NOT work:

 SELECT itf_YYYY_MM(MyDate) [YYYY-MM] FROM DataSource;  

You must jump thru a bit of a hoop.

 SELECT (SELECT [YYYY-MM] FROM itf_YYYY_MM(MyDate)) [YYYY-MM] FROM DataSource;  

The extra code, while not pretty, does in fact produce exactly the same plan as imbedding the original formula into the query. Thus our goal of encapsulation is met. And, generally speaking, any single statement scalar formula can be inlined using this technique.

Fast split function. Turn a delimited list into a rowset.

I’ve not posted in a while so I thought I’d put some queries I’ve found most useful up as a holiday present.

This one splits a delimited list such as ‘this,comma,separated,list,of,words’ into a table with one column and a row for each item in the list.

It’s faster than any other split function I’ve seen but it has the drawback that it might fail if you try to split very very long lists because it convert to XML which lengthens the string considerably.

You can use it, for example, to split up multi-select SSRS params.

A statement like this

SELECT item FROM global.udt_Split('this ,comma ,separated ,list ,of ,words',' ,')

Returns a table this:

item
----------
this
comam
separated
list
of
words

And since it’s an inline function it operates very efficiently in a Cross Apply like this:

SELECT V.item 
  FROM Table_Containing_a_List T
 CROSS Apply global.udt_Split(T.Col_csv,',') V;

Here is the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [global].[udt_Split] (
@Data VARCHAR(max), -- list of delimited values like '1|2|5|7'
@Delimiter VARCHAR(10) -- character(s) seperating elements like '|'
)
 --------------------------------------------------------------------
-- CREATED BY : Rick Bielawski
-- CREATE DATE: 10/23/2010
-- DESCRIPTION: Converts a string of delimited values to a table 
-- with each value on a seperate row.
--------------------------------------------------------------------
RETURNS Table
WITH SCHEMABINDING
AS Return
WITH x AS (SELECT CAST('<root><item>'
+REPLACE (REPLACE (REPLACE (REPLACE(@Data,'&','&amp;'),'<','&lt;'),'>','&gt;')
,REPLACE (REPLACE (REPLACE(@delimiter,'&','&amp;'),'<','&lt;'),'>','&gt;'),'</item><item>')
+'</item></root>' AS XML) as [xml])
SELECT n.c1.value('.[1]','varchar(max)') AS[item]
  FROM x
 CROSS APPLY x.xml.nodes('/root/item') AS n(c1)

Use this to test:

select ''''+item+'''' from global.udt_split('this list,should split,properly, \
even with blanks ,string''s should “behave”,"unbalanced \
or somewhat long strings should not truncate unexpectedly, and imbedded xml \
chars like ><, <or><this><malformed;>''junk''>,should be OK!',',')

Querying information from deployed SSRS packages

It always comes up that people need to know which SSRS reports access a particular table or stored procedure.

They are stored in a database in XML format.  You should be able to query them.  And you can.

I’ve used queries of this form to report on all types of data in deployed RDLs.

 

Note that the name of the database you deploy reports into might be different than the one referenced in these queries.

Also note that the column sizes might need adjusting to accomodate your names. 

The ones I choose were choosen to best fit the width of my screen

 

---------------------------------------------------------------------------------------
-- List all reports that call stored procedures and the name of the procedure called.
---------------------------------------------------------------------------------------
With XMLNAMESPACES(N'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
           default N'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT n.c1.value(N'Query[1]/CommandText[1]',N'varchar(40)')    "Stored Proc"
      ,n.c1.value(N'Query[1]/DataSourceName[1]',N'varchar(15)')"DataSource Name"
      ,n.c1.value(N'@Name',N'varchar(30)')                      "DataSet Name"
      ,cast(path as varchar(80))                                "Report Path"
  from (SELECT path
              ,CAST(CAST(CAST(C.Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) as xml
          FROM ReportServer$.dbo.Catalog C
         WHERE c.Type = 2) as x
 CROSS APPLY x.xml.nodes(N'/Report/DataSets/DataSet') AS n(c1)
 where n.c1.value(N'Query[1]/CommandType[1]',N'varchar(20)') = N'StoredProcedure'
 order by "Stored Proc","Report Path","DataSet Name"

 

 

---------------------------------------------------------------------------
-- List all reports that reference a particular table in a query.
-- Replace <table Name here> below with the reference you are looking for.
---------------------------------------------------------------------------
With XMLNAMESPACES(N'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
           default N'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT cast(path as varchar(75))"Path"
      ,n.c1.value(N'Query[1]/DataSourceName[1]',N'varchar(15)')"DataSource Name"
      ,n.c1.value(N'@Name',N'varchar(25)')"DataSet Name"
      ,REPLACE(n.c1.value(N'Query[1]/CommandText[1]',N'varchar(max)'),CHAR(10),' ')"Query Text"
  from (SELECT path
              ,CAST(CAST(CAST(C.Content AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) as xml
          FROM ReportServer$.dbo.Catalog C
         WHERE c.Type = 2) as x
 CROSS APPLY x.xml.nodes(N'/Report/DataSets/DataSet') AS n(c1)
 WHERE COALESCE(n.c1.value(N'Query[1]/CommandType[1]',N'varchar(20)'),N'') <> N'StoredProcedure'
   AND n.c1.value(N'Query[1]/CommandText[1]',N'varchar(max)') like N'%<table Name here>%'
 ORDER BY "Path","DataSource Name","DataSet Name";
 --

Adding lots of useful DateTime functions

Here are a couple of really useful methods you could add to the [DateTime] type.

 

Even if some are not exactly what you need it provides a framework for adding your own.

 

I’ve added them by putting the code below into a file in $PSHome named

DateTime.Types.ps1xml

Then in my $PSHome\Profile.ps1 I have the line

Update-TypeData (Join-Path $PSHome ‘DateTime.Types.ps1xml’)

Now anyone on the machine with a PowerShell Session has access to the extensions.

<?xml version="1.0" encoding="utf-8" ?>
<!--<# *******************************************************************

  This extension adds complex date manipulation functionality to
  [system.datetime] data types.  Specifically 2 Methods are added.  Each
  returns a [system.datetime] object with the requested value just like
  native methods such as AddYears or AddDays.

  AddBusinessDays([int]$Count)

    Returns the business day $Count days from $this date where 1 means
    next and -1 is prior.  Zero is treated opposite of TruncateTo('BD')
    i.e. it returns $this or the following Monday.  When the date falls
    on a weekend and $Count is not zero Monday or Friday are counted.
    That is <a-Sunday-date>.AddBusinessDays(1) results in Monday and
    <a-Sunday-date>.AddBusinessDays(-1) results in Friday.

  TruncateTo([string]$Part[,[int]$Offset])

    Truncates $this date to the date part named.

      Examples using:      $mydate = Get-Date '12/31/1999 17:47:12.345'
        $mydate.TruncateTo('Year')     Returns 01/01/1999 00:00:00.000
        $mydate.TruncateTo('QY')       Returns 10/01/1999 00:00:00.000
        $mydate.TruncateTo('BD')       Returns 12/31/1999 00:00:00.000
        $mydate.TruncateTo('Second')   Returns 12/31/1999 17:47:12.000
        $mydate.TruncateTo('Y',0)      Returns 01/01/1999 00:00:00.000
        $mydate.TruncateTo('FBDY',1)   Returns 01/03/2000 00:00:00.000
        $mydate.TruncateTo('18:00',1)  Returns 12/31/1999 18:00:00.000

    $Part is the part of the date to truncate to.  Either the abbreviated
          or long (FullySpelledOut) version of the part can be used.
               Y - Year
               M - Month
               D - Day
               H - Hour
              MI - Minute
               S - Second
          ******** Parts of a year
              HY - HalfYear       (Jan,Jul)
              QY - QuarterYear    (Jan,Apr,Jul,Oct)
            FBDY - FirstBusinessDayYear
          ******** Parts of a month
              HM - HalfMonth      (1st or 16th)
            FBDM - FirstBusinessDayMonth
          ******** Parts of a day
              HD - HalfDay        Midnight or Noon (12 hr period)
              TD - ThirdDay       Midnight, 8:am, 4:pm
              QD - QuarterDay     Midnight, 6:am, Noon, 6:pm
              BD - BusinessDay    BOD Today or most recent Fri on Sat or Sun
           hh:mm - SPECIFIC TIME  Some examples: "07:30", "14:00", "23:59"
          ******** Parts of an hour
              HH - HalfHour       :00 or :30 (30 minute period)
              TH - ThirdHour      :00, :20, :40 (20 minute periods)
              QH - QuarterHour    :00, :15, :30, :45 (15 minute periods)
              SH - SixthHour      :00, :10, :20, ... (10 minute periods)
          ******** Parts of a week
             Sun - Sunday         Truncates to most recent Sunday
             Mon - Monday
             Tue - Tuesday
             Wed - Wednesday
             Thu - Thursday
             Fri - Friday
             Sat - Saturday

    Note: Truncation without an offset will never return a future date.

    $Offset ..defaults to 0.  When non-zero, adds that number of $part
              periods to the result.  For example ('Y',2) first truncates
              to the current year then adds 2 years.  ('QH',1) used with
              17:37 yields 17:45 and ('QH',3) gives 18:15.
********************************************************************># -->

<Types>
  <Type>
    <Name>System.DateTime</Name>
    <Members>
      <ScriptMethod>
        <Name>AddBusinessDays</Name>
        <Script>
    param ([int]$Count,[datetime]$From = $([datetime]::MinValue))
    # Use this if params don't seem to get passed
    #[int]$Count = $args[0]
    #[datetime]$From = $([datetime]::MinValue)
    if ($From -eq [datetime]::MinValue) {$From = $This.AddDays(0)}
    while (!$Count -and 1..5 -notcontains $From.DayOfWeek.value__) {
        $From = $From.AddDays(1)}
    if ($Count -lt 0) {
      while ($Count++) {
        $From = $From.AddDays(-1)
        while (1..5 -notcontains $From.DayOfWeek.value__) {
          $From = $From.AddDays(-1)}
      }}
    else {
      while ($Count--) {
        $From = $From.AddDays(1)
        while (1..5 -notcontains $From.DayOfWeek.value__) {
          $From = $From.AddDays(1)}
      }}
    $From
        </Script>
      </ScriptMethod>
      <ScriptMethod>
        <Name>TruncateTo</Name>
        <Script>
    param ([string]$Part,[int]$Offset,[switch]$KeepTime)
    # Use this if params don't seem to get passed
    #[string]$Part = $args[0]
    #[int]$Offset = $args[1]
    #[switch]$KeepTime = $args[2]
    $Date = $this.Date
    $Time = $this.TimeOfDay
    $Base = $Date.AddDays((1-$this.DayOfYear))
    $DowB = [datetime]'1995/01/01'
    $Days = [math]::Truncate(($Date - $DowB).TotalDays)
    switch -regex ($Part) {
      # Parts of DateTime
      '^(0?1|10|Y|Year)$' {
          $Base.AddYears($Offset)}
      '^(0?2|20|M|Month)$' {
          ($Date.AddDays((1-$Date.Day))).AddMonths($Offset)}
      '^(0?3|30|D|Day)$' {
          $Date.AddDays($Offset)}
      '^(0?4|40|H|Hour)$' {
          $Date.AddHours($Time.Hours+$Offset)}
      '^(0?5|Mi|Minute)$' {
          $Date.AddMinutes([math]::Truncate($Time.TotalMinutes)+$Offset)}
      '^(0?6|S|Second)$' {
          $Date.AddSeconds([math]::Truncate($Time.TotalSeconds)+$Offset)}
      # Parts of Year
      '^(11|HY|HalfYear)$' {
          $Base.AddMonths(([math]::Truncate(($Date.Month -1)/6)+$Offset)*6)}
      '^(12|QY|QuarterYear)$' {
          $Base.AddMonths(([math]::Truncate(($Date.Month -1)/3)+$Offset)*3)}
      '^(19|FBDY|FirstBusinessDayYear)$' {
          $t1 = $t2 = $Base
          while (1..5 -notcontains $t1.DayOfWeek.value__)
            {$t1 = $t1.AddDays(1)}
          if ($Date.DayOfYear -lt $t1.DayOfYear) {
            $t2 = $t2.AddYears($Offset - 1)}
          else {
            $t2 = $t2.AddYears($Offset)}
          while (1..5 -notcontains $t2.DayOfWeek.value__)
            {$t2 = $t2.AddDays(1)}
          $t2}
      # Parts of Month
      '^(21|HM|HalfMonth)$' {
          $t = $Date.AddMonths([math]::Truncate($Offset/2))
          if ($t.Day -ge 16) {
            switch ($Offset%2) {
              1 {$t.AddDays(1-$t.Day).AddMonths(1)}
             -1 {$t.AddDays(1-$t.Day)}
              0 {$t.AddDays(16-$t.Day)}}}
          else {
            switch ($Offset%2) {
              1 {$t.AddDays(16-$t.Day)}
             -1 {$t.AddDays(16-$t.Day).AddMonths(-1)}
              0 {$t.AddDays(1-$t.Day)}}}}
      '^(29|FBDM|FirstBusinessDayMonth)$' {
          $t1 = $t2 = $Date.AddDays(1-$Date.Day)
          while (1..5 -notcontains $t1.DayOfWeek.value__)
            {$t1 = $t1.AddDays(1)}
          if ($Date.Day -ge $t1.Day) {
            $t2 = $t2.AddMonths($Offset)}
          else {
            $t2 = $t2.AddMonths($Offset - 1)}
          while (1..5 -notcontains $t2.DayOfWeek.value__) {
            $t2 = $t2.AddDays(1)}
          $t2}
      # Parts of Day
      '^(31|HD|HalfDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/12)+$Offset)*12)}
      '^(32|TD|ThirdDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/8)+$Offset)*8)}
      '^(33|QD|QuarterDay)$' {
          $Date.AddHours(([math]::Truncate($Time.Hours/6)+$Offset)*6)}
      '^(39|BD|BusinessDay)$' {
          $t = $Date
          while (1..5 -notcontains $t.DayOfWeek.value__)
            {$t = $t.AddDays(-1)}
          $this.AddBusinessDays($Offset,$t)}
      # Time of day
      '^[0-9][0-9]?:[0-9][0-9]$' {
          $TimeOfDay = [TimeSpan]$_
          $(if ($this.TimeOfDay -gt $TimeOfDay) {
              $this.date + $TimeOfDay}
            else {$this.date.AddDays(-1) + $TimeOfDay}).AddDays($Offset)}
      # Parts of Hour
      '^(41|HH|HalfHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/30)+$Offset)*30)}
      '^(42|TH|ThirdHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/20)+$Offset)*20)}
      '^(43|QH|QuarterHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/15)+$Offset)*15)}
      '^(44|SH|SixthHour)$' {
          $Date.AddMinutes(([math]::Truncate($Time.TotalMinutes/10)+$Offset)*10)}
      # Parts of Week
      '^(71|Sun|Sunday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -0)/7)+$Offset)*7+0)}
      '^(72|Mon|Monday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -1)/7)+$Offset)*7+1)}
      '^(73|Tue|Tuesday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -2)/7)+$Offset)*7+2)}
      '^(74|Wed|Wednesday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -3)/7)+$Offset)*7+3)}
      '^(75|Thu|Monday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -4)/7)+$Offset)*7+4)}
      '^(76|Fri|Friday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -5)/7)+$Offset)*7+5)}
      '^(77|Sat|Saturday)$' {
          $DowB.AddDays(([math]::Truncate(($Days -6)/7)+$Offset)*7+6)}
      default {
        # The numeric syntax is for testing and unsupported numbers return $null
        if ($_ -notmatch '^[0-9][0-9]?$') {
          throw ("Expecting one of`nY M D H Mi S HY QY FBDY HM FBDM " +
                 "HD TD QD hh:mm HH TH QH SH BD SUN MON TUE WED THU FRI SAT`n" +
                 "Year Month Day Hour Minute Second HalfYear " +
                 "QuarterYear FirstBusinessDayYear HalfMonth`n" +
                 "FirstBusinessDayMonth HalfDay ThirdDay QuarterDay " +
                 "BusinessDay HalfHour ThirdHour QuarterHour SixthHour`n" +
                 "Sunday Monday Tuesday Wednesday Thursday Friday Saturday")}}
    }
        </Script>
      </ScriptMethod>
    </Members>
  </Type>
</Types>

P.S. Looking at the code reveals that $part can be a number (not documented).
    This feature has 2 purposes.
    1) I have a scalar valued function in SQL Server with similar functionality.
       Because that is used in queries I wanted a more efficient arg in its case statement.
       These numbers match those...
    2) It makes for easier testing because I don't need to code all the valid type names.
       For example:

    

$e = [datetime]'2009/11/16 22:47:31.562'

1..80|
        %{'{0:D2}' -f $_}|
        %{if($e.TruncateTo($_)){"$_ = $($e.TruncateTo($_,-2)) |
        $($e.TruncateTo($_,-1)) | $($e.TruncateTo($_)) | $($e.TruncateTo($_,1)) |
        $($e.TruncateTo($_,2))"}}

Determining a file’s EOL character

A simple function to determine the type of EOL character used by a file.

When run against non-ascii encoding systems it can give false readings because the high order byte could be 10 or 13.

While I state this is possible, it should not be a problem for most languages.

function Get-EOL_Type ($FileName,[int]$MaxLen = 2000) {
    $Chars = Get-Content $FileName -encoding byte -totalcount $MaxLen
    if ($Chars -contains 13 -and $Chars -contains 10) {’CRLF’}
    elseif ($Chars -contains 13) {’CR’}
    elseif ($Chars -contains 10) {’LF’}
    else {’unk’}
}

Better than Splat @args

The Splat operator doesn’t support named args and and it cannot be used to pass args to a method.   These are serious drawbacks.

 

Completely solving the named args issue is currently not possible because PowerShell turns switches and argument names into strings and there is no way to tell if a given string actually had quotes or not in the original call statement (see bug report https://connect.microsoft.com/PowerShell/feedback/ViewFeedback.aspx?FeedbackID=368512 ).  If we ignore the possiblity of ambiguous strings (which should be rare anyway) we can come pretty close to a solution.  In this example, $args are being splatted as arguments to ‘Nested-Command’

 

$args|% -begin{$exp = ‘Nested-Command ‘
               $_ctr_ = 0} `
        -process{if($_ -is [string] -and $_ -match ‘^-[a-z0-9_]+$’) {
                   $exp += "$_ "}
                 else {set-variable (‘_tmp_’+ ++$_ctr_) $_
                       $exp += "`$$(‘_tmp_’ + $_ctr_) "} } `
        -end{Invoke-Expression $exp}

This next algorithm does exactly what the existing splat operator does.  That is, it only spreads the args out but doesn’t try to deal with named args.  But methods don’t need named args so it can pass args on to a method just fine.  In this example $args are being splatted as arguments to $Instance.Function

       $args|
         % -begin  {$exp = ‘$Instance.Function(‘
                    $_ctr_ = 0} `
           -process{set-variable (‘_tmp_’+ ++$_ctr_) $_
                    $exp += "`$$(‘_tmp_’ + $_ctr_),"} `
           -end    {$exp = $exp.Trim(‘,’) + ‘)’
                    Invoke-Expression $exp}