World of Whatever

A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Thursday, March 27, 2014

Biml - RebuildIndex Task

Biml - RebuildIndex Task

The maintenance task, Rebuild Index Task, described with Biml. By now you know the drill, it requires an ADO.NET Connection Manager to work.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package 
            ConstraintMode="Linear" 
            Name="Task_RebuildIndex">
            <Tasks>
                <RebuildIndex 
                    ConnectionName="CM_ADO_DB" 
                    DatabaseSelectionMode="All" 
                    ObjectSelectionMode="Tables" 
                    Name="RI All Tables">
                </RebuildIndex>
                
                <!-- 
                    Rebuild the index on a specific view
                -->
                <RebuildIndex
                    ConnectionName="CM_ADO_DB"
                    DatabaseSelectionMode="Specific"
                    ObjectSelectionMode="Views"
                    Name="RI Specific View"
                    ReindexPercentage="50"
                    ReindexWithOriginalAmount="false"
                    SortInTempDB="false"
                    SkipUnsupported="true"
                    KeepOnline="true">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                    </Databases>
                    <Objects>
                        <Object>Production.vProductAndDescription</Object>
                    </Objects>
                </RebuildIndex>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

The above Biml describes a package that creates two Rebuild Index Tasks. The first rebuilds on all the tables while the second targets a specific indexed view.

RI All Tables

Here we reindex all the tables in all the databases.

RI Specific View

This snippet of Biml describes reindexing a specific view, Production.vProductAndDescription in the AdventureWorks2012 database.

Specify Database

Specify the database(s) we should use. Normally, I'd have trimmed out the empty space at the top of the window there but I left it as is to point out the wasted real estate. It's like there's a missing title block.

Specify Object

Here we're selecting the one object we're interested in re-indexing.

Tuesday, March 11, 2014

Biml - Notify Operator Task

Biml - Notify Operator Task

The maintenance task Notify Operator contacts the pre-defined operators. It requires an ADO.NET connection manager to work. Specify the operator, subject and message contents and away you go.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_NotifyOperator">
            <Variables>
                <Variable Name="OperatorName" DataType="String">Ops</Variable>
                <Variable Name="Subject" DataType="String">Look here</Variable>
                <Variable Name="Message" DataType="String">Things have gone south</Variable>
                <Variable Name="Query" DataType="String" EvaluateAsExpression="true">"
DECLARE @OpName sysname = 'Ops';

IF NOT EXISTS
(
    SELECT * 
    FROM msdb.dbo.sysoperators AS SO 
    WHERE SO.name = @OpName
)
EXECUTE msdb.dbo.sp_add_operator 
    @name = @OpName
,   @enabled = 1
,   @pager_days = 0
,   @email_address = N'spam@billfellows.net';
                "</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL 
                    ConnectionName="CM_ADO_DB" 
                    Name="SQL Create Operator">
                    <VariableInput VariableName="User.Query" />
                </ExecuteSQL>
                
                <NotifyOperator 
                    ConnectionName="CM_ADO_DB" 
                    Name="NO Ack">
                    <OperatorsToNotify>
                        <Operator>Ops</Operator>
                    </OperatorsToNotify>
                    <Subject>Ack</Subject>
                    <Message>Body of the notification</Message>
                    <Expressions>
                        <Expression PropertyName="Subject">@[User::Subject]</Expression>
                        <!--<Expression PropertyName="OperatorName">@[User::Subject]</Expression>-->
                        <Expression PropertyName="Message">@[User::Message]</Expression>
                    </Expressions>
                </NotifyOperator>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with an Execute SQL Task to ensure our Operator exists and then an actual Notify Operator Task is dropped into our Control Flow.

NO Ack

This is our Notify Operator task and it acknowledges the errors on the server.

Expression

I could figure out how to configure the message and the subject but couldn't figure out how to set the Operator name via expression.

Monday, March 10, 2014

Biml - Message Queue Task

Biml - Message Queue Task

Why have one tool when you can multiples? In this case, queuing technologies. As a SQL Server professional, I was aware of Service Broker but there's a whole native queuing technology built into Windows: Microsoft Message Queuing. Until doing the research on the SSIS Message Queue Task, I had assumed it pulled from Service Broker. Yeah, well assumptions...

MSMQ Setup

I originally created a private queue on a different box than I was running my package on but the native Message Queue Task can't converse with a remote server. MSDN has a write up on how you can use a script task to talk to a remote private queue

Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <MsmqConnection Name="CM_MSMQ" Path=".\private$\POC" />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_MessageQueue">
            <Variables>
                <Variable Name="MessageSource" DataType="String">What God hath wrought</Variable>
                <Variable Name="MessageReceipt" DataType="String"></Variable>
            </Variables>
            <Tasks>
                <MessageQueue 
                    MsmqConnectionName="CM_MSMQ" 
                    Name="MQ Send Message"
                    >
                    <VariableInput VariableName="User.MessageSource"></VariableInput>
                    <Expressions>
                        <Expression PropertyName="MessageString">@[User::MessageSource]</Expression>
                    </Expressions>
                </MessageQueue>

                <MessageQueue 
                    MsmqConnectionName="CM_MSMQ" 
                    Name="MQ Receive Message"
                    RemoveFromQueue="true"
                    ReceiveMessageTimeOut="30"
                    >
                    <StringVariableOutput VariableName="User.MessageReceipt" />
                </MessageQueue>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with a pair of Message Queue Tasks. One to send and one to receive. There are two Variables: MessageSource and MessageReceipt, both of type String. MessageSource is preloaded with a value of What God hath wrought. After execution, if all went well, that value will be loaded into MessageReceipt. All of this will use our MSMQ connection manager, CM_MSMQ.

CM_MSMQ

Here's a screenshot of what the connection manager looks like. The value of the path is .\private$\POC to indicate the message queue server is local, it's a private queue named POC.

MQ Send Message

A send message task will put our message onto our private queue, POC.

General tab

We have set the task to send a message

Send tab

Sent tab is set to send an unencrypted message. While the StringMessage property appears to be hard coded, that's just an artifact of how the UI works.

Expressions

The MessageString is actually configured based on our @[User::MessageSource] Variable.

MQ Receive Message

A receive message task will pull messages off the queue and assign their value to our Variable.

General tab

The task is configured to receive messages. As you'll see, there's no need for expressions on this task.

Receive tab

In the Receive tab, we remove them from the source queue and assign the message into our Variable User::MessageReceipt.

Execution Results

Green is good. Even better is that the value of @[User::MessageReceipt] matches @[User::MessageSource]

Thursday, March 6, 2014

Biml - Maintenance Cleanup Task

Biml - Maintenance Cleanup Task

The Maintenance Cleanup Task removes artifacts from maintenance plans, backups, reports, etc. As with other Maintenace Tasks, this requires an ADO.NET Connection Manager.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_MaintenanceCleanup">
            <!-- 
                Backup\InstanceName\DBName\DIFF 
                Backup\InstanceName\DBName\FULL 
                Backup\InstanceName\DBName\LOG 
            -->
            <Variables>
                <Variable Name="FileExtension" DataType="String">bacon</Variable>
                <Variable Name="FolderBase" DataType="String">J:\Backup</Variable>
                <Variable Name="InstanceName" DataType="String">WESTMARCH$DEV2012</Variable>
                <Variable Name="DatabaseName" DataType="String">Adventureworks2012</Variable>
                <Variable Name="FolderPath" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::InstanceName] + "\\" + @[User::DatabaseName] </Variable>
            </Variables>
            <Tasks>
                <!--
                    http://stackoverflow.com/questions/11571002/can-someone-help-explain-some-of-the-new-ssis-2012-properties 
                -->
                <MaintenanceCleanup 
                    ConnectionName="CM_ADO_DB" 
                    Name="MC Clean backup files" 
                    DeleteFromAllBricks="true"
                    AgeBased="true"
                    FileTypeSelected="BackupFiles"
                    OlderThanTimeUnits="4"
                    OlderThanTimeUnitType="Weeks"
                    >
                    <!-- Delete all the bacon -->
                    <FolderAndExtension 
                        Folder="C:\DrivenByVariable" 
                        FileExtension="VariableToo"
                        IncludeFirstLevelSubfolders="true"
                    >
                    </FolderAndExtension>
                    <Expressions>
                        <Expression PropertyName="FolderPath">@[User::FolderPath]</Expression>
                        <Expression PropertyName="FileExtension">@[User::FileExtension]</Expression>
                    </Expressions>
                </MaintenanceCleanup>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with Variables to control the FileExtension and FolderPath that are removed. I'm basing the three Variables that build my FolderPath variable based on the default output of Ola's Maintenance plans.

MC Clean Backup Files

Great googly-moogly, someone's attempting to get rid of all the bacon! Well, at least any Backup files that are in the first-level subfolder, with an extension of bacon, that are older than 4 weeks.

Expressions

As you can see, I've applied an Expression to control both FileExtension and FolderPath based on our user Variables.

Wednesday, March 5, 2014

Biml - History Cleanup Task

Biml - History Cleanup Task

The History Cleanup Task is another of those Database Maintenance tasks that I never use. It requires an ADO connection manager and allows you to cleanup any combination of backup and restore history, SQL Server Agent history and Maintenance plan history.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="CM_ADO_DB" ConnectionString="Data Source=localhost\dev2012;Integrated Security=SSPI;Connect Timeout=30;Database=msdb;" Provider="SQL"  />
    </Connections>

    <Packages>
        <Package ConstraintMode="Linear" Name="Task_HistoryCleanup">
            <Tasks>
                <!-- Look busy without doing anything -->
                <HistoryCleanup 
                    ConnectionName="CM_ADO_DB" 
                    Name="HC Clean as my children do"
                    OlderThanTimeUnits="4"
                    OlderThanTimeUnitType="Weeks"
                    RemoveBackupRestoreHistory="false"
                    RemoveDBMaintenanceHistory="false"
                    RemoveSqlAgentHistory="false"
                />
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with our ADO.NET connection manager and a single Cleanup History task.

HC Clean as my children doa

I've configured the History Cleanup task to do nothing.

I love that it actually warns you of this as you click the View T-SQL button

Monday, March 3, 2014

Biml - FTP Task

Biml - FTP Task

SSIS has some really awesome tasks to improve your ability to acquire, manage and push data about. The FTP Task isn't one of them.

I cut my teeth with FTP on a command line so the limited options available via the native task are rather bothersome. Compound that with some of the other annoyances, like erroring when a file does not exist, you can see why I prefer to use a command file.

In this example, we are going to access a publicly available ftp server using anonymous user and blank password. We'll download a file into our working folder and then delete it.

As you might have observed throughout my postings, I really believe the power of SSIS comes through using Expressions. Something that surprised me was that as of this writing, I cannot use an Expression on the OperationName property of an FTP task. Well, I can apply one, but I have no idea what the text string that should go inside of one of those. Instead, by editing an existing package's operationg via Visual Studio and then viewing the resulting XML, I was able to discern the correlation between the displayed text and the underlying Operation numeric value.

0
Send file
1
Receive file
2
Delete Local Files
3
Delete Remote Files
4
Create Local Directory
5
Create Remote Directory
6
Remove Local Directory
7
Remove Remote Directory

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FtpConnection Name="CM_FTP" ServerName="localhost" UserName="user" Password="pass" ServerPort="21" UsePassiveMode="true" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_FTP">
            <Connections>
                <Connection ConnectionName="CM_FTP">
                    <Expressions>
                        <Expression PropertyName="CM_FTP.ServerName">@[User::ServerName]</Expression>
                        <Expression PropertyName="CM_FTP.ServerPort">@[User::ServerPort]</Expression>
                        <Expression PropertyName="CM_FTP.ServerUserName">@[User::ServerUserName]</Expression>
                        <!--<Expression PropertyName="CM_FTP.ServerPassword">@[User::ServerPassword]</Expression>-->
                        <Expression PropertyName="CM_FTP.Timeout">@[User::Timeout]</Expression>
                        <Expression PropertyName="CM_FTP.UsePassiveMode">@[User::UsePassiveMode]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Variables>
                <Variable Name="ServerName" DataType="String">ftp2.census.gov</Variable>
                <Variable Name="ServerPort" DataType="Int32">21</Variable>
                <Variable Name="ServerUserName" DataType="String">anonymous</Variable>
                <Variable Name="ServerPassword" DataType="String">
                    <Annotations>
                        <Annotation AnnotationType="Description">Don't ever do this</Annotation>
                    </Annotations>
                </Variable>
                <Variable Name="Timeout" DataType="Int32">60</Variable>
                <Variable Name="UsePassiveMode" DataType="Boolean">False</Variable>
                <Variable Name="LocalFolder" DataType="String">C:\ssisdata</Variable>
                <Variable Name="LocalFile" DataType="String">p3p.xml</Variable>
                <Variable Name="RemoteFolder" DataType="String">/w3c</Variable>
                <Variable Name="RemoteFile" DataType="String">p3p.xml</Variable>
                <!-- 
                0 Send file 
                1 Receive file
                2 Delete Local Files
                3 Delete Remote Files
                4 Create Local Directory
                5 Create Remote Directory
                6 Remove Local Directory
                7 Remove Remote Directory
                -->
                <Variable Name="Operation" DataType="Int32">1</Variable>
                <!--
                The text I have been using does not seem to align with values
                -->
                <Variable Name="OperationName" DataType="String">Receive</Variable>
                <Variable Name="OperationName2" DataType="String">Receive files</Variable>
                <Variable Name="RemotePath" DataType="String" EvaluateAsExpression="true">@[User::RemoteFolder] + "/" + @[User::RemoteFile]</Variable>
                <Variable Name="LocalPath" DataType="String" EvaluateAsExpression="true">@[User::LocalFolder] + "\\" + @[User::LocalFile]</Variable>
                <Variable Name="IsTransferTypeASCII" DataType="Boolean">True</Variable>
                <Variable Name="OverwriteDestination" DataType="Boolean">True</Variable>
            </Variables>
            <Tasks>
                <Ftp 
                    ConnectionName="CM_FTP" 
                    Operation="Send" 
                    Name="FTP Receive File"
                    IsTransferTypeAscii="false"
                    OverwriteDestination="true"
                    >
                    <VariableInput VariableName="User.LocalFolder"></VariableInput>
                    <VariableRemotePath VariableName="User.RemotePath" />
                    <Expressions>
                        <!-- I must not be using the right text here-->
                        <!--<Expression PropertyName="OperationName">@[User::OperationName]</Expression>-->
                        <Expression PropertyName="Operation">@[User::Operation]</Expression>
                        <Expression PropertyName="IsTransferTypeASCII">@[User::IsTransferTypeASCII]</Expression>
                        <Expression PropertyName="OverwriteDestination">@[User::OverwriteDestination]</Expression>
                    </Expressions>
                </Ftp>

                <Ftp 
                    ConnectionName="CM_FTP" 
                    Operation="Send" 
                    Name="FTP Delete downloaded file">
                    <VariableInput VariableName="User.LocalPath"></VariableInput>
                    <Expressions>
                        <Expression PropertyName="Operation">2</Expression>
                    </Expressions>
                </Ftp>

                <Ftp
                    ConnectionName="CM_FTP"
                    Operation="Send"
                    Name="FTP Receive Expression on OperationName"
                    IsTransferTypeAscii="false"
                    OverwriteDestination="true"
                    >
                    <VariableInput VariableName="User.LocalFolder"></VariableInput>
                    <VariableRemotePath VariableName="User.RemotePath" />
                    <Expressions>
                        <!-- I must not be using the right text here-->
                        <Expression PropertyName="OperationName">@[User::OperationName]</Expression>
                        <Expression PropertyName="IsTransferTypeASCII">@[User::IsTransferTypeASCII]</Expression>
                        <Expression PropertyName="OverwriteDestination">@[User::OverwriteDestination]</Expression>
                    </Expressions>
                </Ftp>                
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

A package is created with an FTP connection manager, expressions applied to it, and 3 FTP Tasks.

FTP Receive File

A task is created to receive, GET, a file. This operation type is set directly in the Biml as a Send but we later override it via Expresions.

Expressions

We're going to set the operation type by the numeric value of the Operation property.

FTP Delete downloaded file

An FTP task set to Delete Local Files

FTP Receive Expression on OperationName

This step is identical to the first except in our application of Expressions.

Expressions

You'll observe the Operation remains "Send files" despite setting OperationName to either the value of "Receive" or "Receive files"

Tuesday, February 25, 2014

Biml - File System Task

Biml - File System Task

The File System Task allows you to manipulate objects within the file system without resorting to a Script Task or the Execute Process Task.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package ConstraintMode="Linear" Name="Task_FileSystem">
            <Variables>
                <Variable Name="FileName" DataType="String">system.ini</Variable>
                <Variable Name="FolderBase" DataType="String">J:\SSISData</Variable>
                <Variable Name="FocusArea" DataType="String">Sales</Variable>
                <Variable Name="FolderInput" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Input"</Variable>
                <Variable Name="FolderArchive" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Archive"</Variable>
                <Variable Name="FolderOutput" DataType="String" EvaluateAsExpression="true">@[User::FolderBase] + "\\" + @[User::FocusArea] + "\\Output"</Variable>
                <Variable Name="FileSource" DataType="String" EvaluateAsExpression="true">"J:\\Windows" + "\\" + @[User::FileName]</Variable>
                <Variable Name="FileDestination" DataType="String" EvaluateAsExpression="true">@[User::FolderArchive] + "\\" + @[User::FileName]</Variable>
                <Variable Name="FileRenamed" DataType="String" EvaluateAsExpression="true">@[User::FolderArchive] + "\\MaximumOutput.txt"</Variable>
                <Variable Name="OverwriteDestinationFile" DataType="Boolean">True</Variable>
            </Variables>

            <Tasks>
                <FileSystem 
                    Operation="CreateDirectory" 
                    Name="FS CreateDirectory">
                    <VariableInput VariableName="User.FolderArchive"/>
                    <Annotations>
                        <Annotation AnnotationType="Description">Create a folder based on our Variable</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem 
                    Operation="CopyFile" 
                    Name="FS CopyFile">
                    <VariableInput VariableName="User.FileSource" />
                    <VariableOutput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Copy a file from source to destination</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem 
                    Operation="RenameFile" 
                    Name="FS RenameFile">
                    <VariableInput VariableName="User.FileDestination"/>
                    <VariableOutput VariableName="User.FileRenamed"/>
                    <Annotations>
                        <Annotation AnnotationType="Description">Rename the copied file to something else</Annotation>
                    </Annotations>
                    <Expressions>
                        <Expression PropertyName="OverwriteDestinationFile">@[User::OverwriteDestinationFile]</Expression>
                    </Expressions>
                </FileSystem>

                <FileSystem
                    Operation="DeleteDirectoryContent"
                    DelayValidation="true"
                    Name="FS DeleteDirectoryContent">
                    <VariableInput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Delete everything in the folder</Annotation>
                    </Annotations>
                </FileSystem>
                
                <FileSystem
                    Operation="DeleteDirectory"
                    Name="FS DeleteDirectory">
                    <VariableInput VariableName="User.FolderArchive" />
                    <Annotations>
                        <Annotation AnnotationType="Description">Remove the folder we just created</Annotation>
                    </Annotations>
                </FileSystem>
                    
            </Tasks>
        </Package>
    </Packages>
</Biml>

Result

That mess of biml creates 5 file system tasks. We create a set of folders \SSISData\Sales\Archive. Into the Archive folder, we will copy the system.ini file from the Windows folder. The next task renames it to MaximumOutput only to have all the contents of the Archive folder deleted prior to completely deleting the folder.

FS CreateDirectory

Create a directory, if it does not exist based on our Variable @[User::FolderArchive]

FS CopyFile

Using variables, we will copy our file, defined as @[User::FileSource] to our @[User::FolderArchive]

FS RenameFile

Rename our file from @[User::FileDestination] to @[User::FileRenamed]

FS DeleteDirectoryContent

Purge all the files in @[User::FolderArchive]

FS DeleteDirectory

Finally we drop the folder @[User::FolderArchive]