SSIS: Export all packages from MSDB package store

Quick script to generate DTUTIL statements to export all SSIS package from the MSDB package store to the file system using DTUTIL:

;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 ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\\' + P.PackageName + '" /En file;"F:\\Archief\\SSIS\\'+CONVERT(VARCHAR(50),GETDATE(),112)+'\\' + P.PackageName +'.dtsx";4 /Q' 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'
and F.FolderPath not Like 'Maintenance%'

Till Next Time (Also posted on MSSQL101.BLOGSPOT.COM)

Script with OLE DB connections

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
using Wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;
// Add referenc in menu Project / Add Refference / Microsoft.SqlServer.DtsRuntimeWrap
#endregion

namespace ST_fb053e9cb2994d049ff4ca5a92cd1b7a
{
       [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
       
        public void Main()
        {
         
            string PackageName ="";
            string ExecutionGuid ="";
            string CounterName ="";
            string CounterValue ="";
        

            for (int i = 0; i <= (Dts.Variables.Count -1); i++)
            {
                if (Dts.Variables[i].Name.StartsWith("PackageName") )
                {
                     PackageName = Dts.Variables[i].Value.ToString();
                }

                 if (Dts.Variables[i].Name.StartsWith("ExecutionInstanceGUID") )
                {
                    ExecutionGuid = Dts.Variables[i].Value.ToString();
                }
            }


            for (int i = 0; i <= (Dts.Variables.Count - 1); i++)
            {
                if (Dts.Variables[i].Name.StartsWith("RC"))
                {
                   
                    CounterName = System.Text.RegularExpressions.Regex.Replace(Dts.Variables[i].Name.ToString().Substring(2), "[A-Z]", " $0");
                    CounterValue = Dts.Variables[i].Value.ToString();
                    //MessageBox.Show(CounterName);
                    string CommandText = "";
                    CommandText = "INSERT INTO MGT.[Audit].[ProcessCounts] ([ExecutionGuid],[PackageName],[CounterName],[Value])";
                    CommandText = CommandText + "VALUES('" + ExecutionGuid + "','" + PackageName + "','" + CounterName + "'," + CounterValue + ")";
                    ConnectionManager cm = Dts.Connections["Cn_MGT"];
                    Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;

                    using (OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection)
                    {
                        OleDbCommand command = new OleDbCommand(CommandText, conn);
                        command.ExecuteNonQuery();
                    }

                  
                }

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

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Till Next Time

SSIS Script Database objects:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
' compile with:
' /r:Microsoft.SqlServer.Smo.dll
' /r:Microsoft.SqlServer.ConnectionInfo.dll
' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
Imports Microsoft.SqlServer.Management.Sdk.Sfc


_
_
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
  

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts property. Connections, variables, events,
    ' and logging features are available as members of the Dts property as shown in the following examples.
    '
    ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
    ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
    ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
    '
    ' To use the connections collection use something like the following:
    ' ConnectionManager cm = Dts.Connections.Add("OLEDB")
    ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
    '
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Help, press F1.

    Public Sub Main()
        '
        ' Add your code here
        '
        CreateDBScript()
        Dts.TaskResult = ScriptResults.Success
    End Sub

    Public Sub CreateDBScript()
        ' Connect to the local, default instance of SQL Server.
        Dim srv As New Server(Dts.Variables("DBInstanceName").Value.ToString)

        'Get DB's to script
        Dim scDB() As String
        scDB = Split(Dts.Variables("DBToScript").Value.ToString, ";")
        'MsgBox(Dts.Variables("DBToScript").Value.ToString)
        'Open the Script File
        Dim scrDB As New StreamWriter(Dts.Variables("ScriptOutputFileName").Value.ToString, False)

        'Get all the databases
        Dim scrp As New Scripter(srv)
        scrDB.WriteLine("-- Scripting databases for  " + Dts.Variables("DBInstanceName").Value.ToString)

        For Each dbn As Database In srv.Databases
            If dbn.IsSystemObject = False Then
                Dim sc As System.Collections.Specialized.StringCollection = dbn.Script
                For Each st As String In sc
                    scrDB.WriteLine(st)
                Next
                scrDB.WriteLine("--")
                scrDB.WriteLine("")
            End If
        Next




        'Loop the array
        For i As Integer = scDB.GetLowerBound(0) To scDB.GetUpperBound(0)

            ' database name
            Dim dbName As [String] = scDB(i)  ' database name

            ' Reference the database.
            Dim db As Database = srv.Databases(dbName)

            ' Define a Scripter object and set the required scripting options.

            scrp.Options.ScriptDrops = False
            scrp.Options.WithDependencies = True
            scrp.Options.Indexes = True   ' To include indexes
            scrp.Options.DriAllConstraints = True   ' to include referential constraints in the script
            scrp.Options.SchemaQualify = True
            scrp.Options.ExtendedProperties = True

            scrDB.WriteLine("-- Scripting schemas for  " + dbName)

            For Each sch As Schema In db.Schemas

                If sch.IsSystemObject = False Then
                    ' Generating script for schema sch
                    Dim sc As System.Collections.Specialized.StringCollection = sch.Script
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next

                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")
                End If
            Next

            scrp.Options.WithDependencies = True

            ' Iterate through the tables in database and script each one. Display the script.
            For Each tb As Table In db.Tables
                ' check if the table is not a system table
                If tb.IsSystemObject = False Then
                    scrDB.WriteLine("-- Scripting for table " + tb.Name)

                    ' Generating script for table tb
                    Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {tb.Urn})
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next
                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")


                End If
            Next
            scrp.Options.WithDependencies = False
            ' Iterate through the views in database and script each one. Display the script.
            For Each vw As View In db.Views
                ' check if the table is not a system table
                If vw.IsSystemObject = False Then
                    scrDB.WriteLine("-- Scripting for view " + vw.Name)

                    ' Generating script for table tb
                    Dim sc As System.Collections.Specialized.StringCollection = scrp.Script(New Urn() {vw.Urn})
                    For Each st As String In sc
                        scrDB.WriteLine(st)
                    Next
                    scrDB.WriteLine("--")
                    scrDB.WriteLine("")
                End If
            Next

        Next i
        scrDB.Close()
    End Sub

End Class

SSIS carriage return in expression builder

Found it on MSDN:
Property Expression for the Message of an E-mail Message


The following property expression can be used to set the MessageSource property of a Send Mail task. The expression uses a combination of string literals, user-defined variables, and the concatenation (+) operator. The user-defined variables are named nasdaqrawrows, nyserawrows, and amexrawrows. The string "\n" indicates a carriage return.

"Rows Processed: " + "\n" +" NASDAQ: " + (dt_wstr,9)@[nasdaqrawrows] + "\n" + " NYSE: " + (dt_wstr,9)@[nyserawrows] + "\n" + " Amex: " + (dt_wstr,9)@[amexrawrows]


If nasdaqrawrows is 7058, nyserawrows is 3528, and amexrawrows is 1102, the expression evaluates to the following string.


Rows Processed:



NASDAQ: 7058



NYSE: 3528



AMEX: 1102

SSIS Retain Connection

When you connect to an Oracle database, you sometimes need to keep the connection to stay in the same session:

It’s simple, goto the properties of your connection and set RetatinSameConnection to TRUE:

Till Next Time

SSIS Dynamic File Name

"D:\\TEMP\\FilePrefix_" + (DT_WSTR,30)(YEAR(GETDATE()) ) + RIGHT ( "0"+(DT_WSTR,30)(MONTH(GETDATE()) ) ,2) +RIGHT("0"+ (DT_WSTR,30)(DAY(GETDATE()) ),2)+ RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Hh",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "Mi",GETDATE())),2) +RIGHT ("0" + (DT_WSTR,30) (DATEPART( "ss",GETDATE())),2) +".TXT"
Till Next Time

SSIS Generating new package GUID

Remember to generate a new package id when using a package template or a copy:

Till Next Time