SSIS Documentation

Contents

• Loop Containers
  • counter
  • time container
    • Does File Exist
  • Data Flow Task
    • Components
      • Recordset Destination
      • OLE DB Source
  • Sending Mails from a recordset
    • Send Mail Task
      • Script Task
  • Connections
    • Email Connection
    • LocalHost.Pass2005Munich
    • MyLogFile.txt

Loop Containers Version: 1.0.65

Property	Value
PackageFormatVersion	2
VersionComments
CreatorName	VM_XP_LAP\Administrator
CreatorComputerName	VM_XP_LAP
CreationDate	3/27/2005 12:20:33 PM
PackageType	DTS Designer 90
ProtectionLevel	1
MaxConcurrentExecutables	-1
PackagePriorityClass	0
EnableConfig
CheckpointFileName
SaveCheckpoints
CheckpointUsage	0
SuppressConfigurationWarnings
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	English - United Kingdom
TransactionOption	Supported
DelayValidation
ObjectName	Loop Containers
Description
DisableEventHandlers
AddingInTheForLoop	0
CustomerFullName
DoesFileExist	0
EmployeeEmailAdress
FileToCheckFor	c:\Allan.txt
ForEachVariableEnumerator	0
LeadClosureDate	1/1/1900
LoopADO
RecordsetDest
ToLine
UserForLoopVariable	12:00:00 AM
LoggingMode	Enabled

Connections

• SMTP: Email Connection
• OLEDB: LocalHost.Pass2005Munich

Property	Value
Retain
ConnectionString	Data Source=.;Initial Catalog=PASS Europe 2005;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;

• FILE: MyLogFile.txt

Property	Value
FileUsageType	1
ConnectionString	C:\Documents and Settings\Administrator\Desktop\MyLogFile.txt

counter

(For Loop Container)

Property	Value
InitExpression	@AddingInTheForLoop = 1
EvalExpression	@AddingInTheForLoop <= 10
AssignExpression	@AddingInTheForLoop = @AddingInTheForLoop + 1
MaxConcurrent	1
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	counter
Description	For Loop Container
DisableEventHandlers
LoggingMode	Use Parent Setting

time container

(For Loop Container)

Property	Value
InitExpression	@UserForLoopVariable = DATEADD("minute",10,Getdate())
EvalExpression	Getdate() <= @UserForLoopVariable && @DoesFileExist == False
AssignExpression
MaxConcurrent	1
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	time container
Description	For Loop Container
DisableEventHandlers
LoggingMode	Use Parent Setting

Does File Exist

(Script Task)

Property	Value
ExecutionLocation	0
ExecutionAddress
TaskContact
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	Does File Exist
Description	Script Task
DisableEventHandlers
LoggingMode	Use Parent Setting
Script Project
Name	ScriptTask_55eb736b33f44ce3a1b48b6476fd1969
Language	Microsoft Visual Basic .NET
EntryPoint	ScriptMain
SaveBinaries	False
ReadOnlyVariables	FileToCheckFor
ReadWriteVariables	DoesFileExist
Item: dts://Scripts/ScriptTask_55eb736b33f44ce3a1b48b6476fd1969/ScriptMain.vsaitem ' Microsoft Data Transformation Services (DTS) Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Public Class ScriptMain ' The DTS execution engine calls this method when the task executes. ' To access the DTS object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' Dts.Variables("DoesFileExist").Value = File.Exists(Dts.Variables("FileToCheckFor").Value.ToString()) ' Dts.TaskResult = Dts.Results.Success End Sub End Class Item: dts://Scripts/ScriptTask_55eb736b33f44ce3a1b48b6476fd1969/ScriptTask_55eb736b33f44ce3a1b48b6476fd1969.vsaproj <VisualStudioProject> <VisualBasic Version = "8.0.50215.44" MVID = "{5E330FA8-DBE5-4AA9-AE04-43FCF7F56C76}" ProjectType = "Local" ProductVersion = "8.0.50215" SchemaVersion = "2.0" > <Build> <Settings DefaultNamespace = "ScriptTask_55eb736b33f44ce3a1b48b6476fd1969" OptionCompare = "0" OptionExplicit = "1" OptionStrict = "1" ProjectName = "ScriptTask_55eb736b33f44ce3a1b48b6476fd1969" ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\" TreatWarningsAsErrors = "false" WarningLevel = "1" RootNamespace = "ScriptTask_55eb736b33f44ce3a1b48b6476fd1969" > <Config Name = "Debug" DefineConstants = "" DefineDebug = "true" DefineTrace = "true" DebugSymbols = "true" RemoveIntegerChecks = "false" /> </Settings> <References> <Reference Name = "System" AssemblyName = "System" /> <Reference Name = "System.Data" AssemblyName = "System.Data" /> <Reference Name = "Microsoft.SqlServer.ScriptTask" AssemblyName = "Microsoft.SqlServer.ScriptTask" /> <Reference Name = "Microsoft.SqlServer.ManagedDTS" AssemblyName = "Microsoft.SqlServer.ManagedDTS" /> </References> <Imports> <Import Namespace = "Microsoft.VisualBasic" /> </Imports> </Build> <Files> <Include> <File RelPath = "ScriptMain" BuildAction = "Compile" ItemType = "2" /> <VSAAppGlobal VSAAppGlobalName = "Dts" ItemType = "1" VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel" /> </Include> </Files> <Folders> <Include/> </Folders> </VisualBasic> </VisualStudioProject>

Data Flow Task

(Data Flow Task)

Pipeline pipelineXml

Property	Value
ExecutionLocation	0
ExecutionAddress
TaskContact	Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	Data Flow Task
Description	Data Flow Task
DisableEventHandlers
LoggingMode	Use Parent Setting

Component Recordset Destination
VariableName	RecordsetDest
Component OLE DB Source
CommandTimeout	0
OpenRowset	[HumanResources].[Employee]
OpenRowsetVariable
SqlCommand	select * FROM dbo.vw_LeadsNotFollowed
SqlCommandVariable
DefaultCodePage	1252
AccessMode	2
ParameterMapping
AlwaysUseDefaultCodePage	false
Connections
OleDbConnection
OLE DB Source Output
Name Description Type Length Precision Scale EmployeeEmailAddress str 200 0 0 LeadName str 200 0 0 LeadExpiryDate30Days dbTimeStamp 0 0 0 OLE DB Source Output
OLE DB Source Error Output
Name Description Type Length Precision Scale EmployeeEmailAddress str 200 0 0 LeadName str 200 0 0 LeadExpiryDate30Days dbTimeStamp 0 0 0 ErrorCode i4 0 0 0 ErrorColumn i4 0 0 0 OLE DB Source Error Output

Sending Mails from a recordset

(Foreach Loop Container)

Property	Value
MaxConcurrent	1
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	Sending Mails from a recordset
Description	Foreach Loop Container
DisableEventHandlers
EnumeratedValue
LoggingMode	Use Parent Setting

Send Mail Task

(Send Mail Task)

Property	Value
ExecutionLocation	0
ExecutionAddress
TaskContact	Send Mail Task; Microsoft Corporation; Microsoft SQL Server v9; © 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	Send Mail Task
Description	Send Mail Task
DisableEventHandlers
LoggingMode	Use Parent Setting

Event Handlers

Property	Value
EventID	0
EventName	OnError
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
Description
Propagate	-1
LoggingMode	Use Parent Setting

Script Task

(Script Task)

Property	Value
ExecutionLocation	0
ExecutionAddress
TaskContact
ForceExecValue
ExecValue	0
ForceExecutionResult	-1
Disabled
FailPackageOnFailure
FailParentOnFailure
MaxErrorCount	1
ISOLevel	1048576
LocaleID	Default
TransactionOption	Supported
DelayValidation
ObjectName	Script Task
Description	Script Task
DisableEventHandlers
LoggingMode	Use Parent Setting
Script Project
Name	ScriptTask_321913ef63a34c5387f96adf6a0f4060
Language	Microsoft Visual Basic .NET
EntryPoint	ScriptMain
SaveBinaries	False
ReadOnlyVariables	ErrorDescription
ReadWriteVariables
Item: dts://Scripts/ScriptTask_321913ef63a34c5387f96adf6a0f4060/ScriptMain.vsaitem ' Microsoft Data Transformation Services (DTS) Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ' The DTS execution engine calls this method when the task executes. ' To access the DTS object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() ' MsgBox("There was an error sending mail " + vbCrLf + "This is the error from the Error Event handler" + vbCrLf + Dts.Variables("ErrorDescription").Value.ToString(), MsgBoxStyle.OKOnly, "Handler return from Sendmail task") ' Dts.TaskResult = Dts.Results.Success End Sub End Class Item: dts://Scripts/ScriptTask_321913ef63a34c5387f96adf6a0f4060/ScriptTask_321913ef63a34c5387f96adf6a0f4060.vsaproj <VisualStudioProject> <VisualBasic Version = "8.0.50215.44" MVID = "{9A2447F8-A4E6-4E5E-B724-3A85032129E7}" ProjectType = "Local" ProductVersion = "8.0.50215" SchemaVersion = "2.0" > <Build> <Settings DefaultNamespace = "ScriptTask_321913ef63a34c5387f96adf6a0f4060" OptionCompare = "0" OptionExplicit = "1" OptionStrict = "1" ProjectName = "ScriptTask_321913ef63a34c5387f96adf6a0f4060" ReferencePath = "C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\" TreatWarningsAsErrors = "false" WarningLevel = "1" RootNamespace = "ScriptTask_321913ef63a34c5387f96adf6a0f4060" > <Config Name = "Debug" DefineConstants = "" DefineDebug = "true" DefineTrace = "true" DebugSymbols = "true" RemoveIntegerChecks = "false" /> </Settings> <References> <Reference Name = "System" AssemblyName = "System" /> <Reference Name = "System.Data" AssemblyName = "System.Data" /> <Reference Name = "Microsoft.SqlServer.ScriptTask" AssemblyName = "Microsoft.SqlServer.ScriptTask" /> <Reference Name = "Microsoft.SqlServer.ManagedDTS" AssemblyName = "Microsoft.SqlServer.ManagedDTS" /> </References> <Imports> <Import Namespace = "Microsoft.VisualBasic" /> </Imports> </Build> <Files> <Include> <File RelPath = "ScriptMain" BuildAction = "Compile" ItemType = "2" /> <VSAAppGlobal VSAAppGlobalName = "Dts" ItemType = "1" VSAAppGlobalType = "Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel" /> </Include> </Files> <Folders> <Include/> </Folders> </VisualBasic> </VisualStudioProject>