What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
Greg
What recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
Showing posts with label packages. Show all posts
Showing posts with label packages. Show all posts
Monday, March 19, 2012
Sunday, March 11, 2012
Controlling log file size
What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
--
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
Controlling log file size
What's the best practice to keep log file size low. I have some databases
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
that run DTS packages to import data every night. The logs get huge, 17
gigs. I have an Arcserve agent backing them up, but it does not seem to
shrink them.
Any ideas?
Thanks
GregWhat recovery model are you using? Do you care about your transaction logs?
If you don't care about the data within the logs you can set your recovery
model to simple. You can also perform a BACKUP LOG <databasename> WITH
NO_LOG between each export to clear the transaction log.
NOTE: these steps will break transaction log backup/restore. Make sure that
you know what you are doing and how it will impact your database backups
before setting the recovery model or truncating the transaction log.
Keith
"Greg Richards" <grichards@.matrixwebs.com> wrote in message
news:uyL9M0FYEHA.3520@.TK2MSFTNGP10.phx.gbl...
> What's the best practice to keep log file size low. I have some databases
> that run DTS packages to import data every night. The logs get huge, 17
> gigs. I have an Arcserve agent backing them up, but it does not seem to
> shrink them.
> Any ideas?
> Thanks
> Greg
>
Friday, February 24, 2012
ContainerStartTime & EventHandlerStartTime are the same
Hello,
In my packages, System::ContainerStartTime & System::EventHandlerStartTime always contain the same value. The value in System::EventHandlerStartTime appears to be correct, so what's going on with System::ContainerStartTime?
I've built a very simple package that demos this using message boxes. Save the text below to a .dtsx file and run it - you'll see what I mean. [Its very very easy to run.]
N.B. I would expect ContainerStartTime to contain the time that the container that raised the event starts to execute - in the case below that container is a TaskHost container.
Thanks
Jamie
=========================================================
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">INT\Jamie.Thomson</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">CGOJTHOMSON</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">8/18/2005 2:02:39 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">2</DTS:Property><DTS:Property DTS:Name="VersionGUID">{3D5387D8-62CB-443D-B268-EA51F125551A}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>4249</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="20161" y="11800" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Script Task" left="11932" top="4763" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{860A9BFA-195D-4655-A494-845091CCCB5D}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{A43E223E-5314-4BC3-BD2E-0FD7A095B9E2}</DTS:Property><DTS:Property DTS:Name="DTSID">{D09E77CA-5C8D-4910-9DFB-4CCB5C94EEE8}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><DtsEventHandler xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsEventHandlerDiagram><dwd:BoundingTop>3651</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="20161" y="10319" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01000000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Script Task" left="8837" top="3651" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{86F387C8-2D2D-48AB-A88C-978F131EAF05}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsEventHandlerDiagram></DtsEventHandler></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{C05EB938-D547-4AFE-9ED9-4ADCC4416BB2}</DTS:Property><DTS:Property DTS:Name="DTSID">{5D639589-B6F3-4E58-BBDB-5ABBC0F0D55B}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">2057</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Script Task</DTS:Property><DTS:Property DTS:Name="DTSID">{860A9BFA-195D-4655-A494-845091CCCB5D}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property>
<DTS:EventHandler><DTS:Property DTS:Name="EventID">0</DTS:Property><DTS:Property DTS:Name="EventName">OnPostExecute</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:Variable><DTS:Property DTS:Name="Expression"></DTS:Property><DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property><DTS:Property DTS:Name="Namespace">System</DTS:Property><DTS:Property DTS:Name="ReadOnly">0</DTS:Property><DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property><DTS:VariableValue DTS:DataType="11">16748</DTS:VariableValue><DTS:Property DTS:Name="ObjectName">Propagate</DTS:Property><DTS:Property DTS:Name="DTSID">{249FD897-7CFC-4E72-8C82-224B402F76B1}</DTS:Property><DTS:Property DTS:Name="Description">The propagate property of the event</DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Variable>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Script Task</DTS:Property><DTS:Property DTS:Name="DTSID">{86F387C8-2D2D-48AB-A88C-978F131EAF05}</DTS:Property><DTS:Property DTS:Name="Description">Script Task</DTS:Property><DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><ScriptProject Name="ScriptTask_51d9bda65709488695cba66c677319d6" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="System::ContainerStartTime,System::EventHandlerStartTime" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_51d9bda65709488695cba66c677319d6/ScriptTask_51d9bda65709488695cba66c677319d6.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_51d9bda65709488695cba66c677319d6"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_51d9bda65709488695cba66c677319d6"
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_51d9bda65709488695cba66c677319d6"
>
<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>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_51d9bda65709488695cba66c677319d6/ScriptMain.vsaitem">
<![CDATA[Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
MsgBox("ContainerStartTime=" + Dts.Variables("System::ContainerStartTime").Value.ToString)
MsgBox("EventHandlerStartTime=" + Dts.Variables("System::EventHandlerStartTime").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="DTSID">{C05EB938-D547-4AFE-9ED9-4ADCC4416BB2}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OnPostExecute</DTS:Property></DTS:EventHandler><DTS:ObjectData><ScriptProject Name="ScriptTask_08d17880975e460299f8d0193ea1ada5" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="False" ReadOnlyVariables="" ReadWriteVariables=""><ProjectItem Name="dts://Scripts/ScriptTask_08d17880975e460299f8d0193ea1ada5/ScriptTask_08d17880975e460299f8d0193ea1ada5.vsaproj">
<![CDATA[<VisualStudioProject>
<VisualBasic
Version = "8.0.50215.44"
MVID = "{00000000-0000-0000-0000-000000000000}"
ProjectType = "Local"
ProductVersion = "8.0.50215"
SchemaVersion = "2.0"
>
<Build>
<Settings
DefaultNamespace = "ScriptTask_08d17880975e460299f8d0193ea1ada5"
OptionCompare = "0"
OptionExplicit = "1"
OptionStrict = "1"
ProjectName = "ScriptTask_08d17880975e460299f8d0193ea1ada5"
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_08d17880975e460299f8d0193ea1ada5"
>
<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>]]></ProjectItem><ProjectItem Name="dts://Scripts/ScriptTask_08d17880975e460299f8d0193ea1ada5/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services 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
Public Sub Main()
MsgBox("Wait a few seconds before clicking OK...")
Dts.TaskResult = Dts.Results.Success
End Sub
End Class]]></ProjectItem></ScriptProject></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">EventHandlerStartTime</DTS:Property><DTS:Property DTS:Name="DTSID">{A43E223E-5314-4BC3-BD2E-0FD7A095B9E2}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>
Right, I've worked out what's going on here. When you access System::ContainerStartTime in the event handler then you are accessing the starttime of the container in which the event handler executes. So OF COURSE this is going to be the same as EventHandlerStartTime.I can't be sure but I think this has changed from previously when ContainerStartTime always contained the start time of the container that raised the event.
Anyway, I have now noticed that there are 2 instances of System::ContainerStartTime in my event handler. One of them is scoped to the container to which the event handler is attached and the other is scoped to the script task that I have in my event handler. By default whenever I reference System::ContainerStartTime it defaults to the one scoped most locally.
How can I reference the System::ContainerStartTime that is scoped to the container that raised the event?
Or to put it another way....if I have 2 identically named variables of differing scope how can I pick which one I want to use?
Regards
Jamie
Friday, February 10, 2012
Consolidating multiple lookups
In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)
Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.
Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?The bullet to bite is to use sub-packages more frequently.
If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)
A second unit of re-use is a custom transform (not a script transform). THe script transform has "cut and paste" inheritence, which is no inheritance at all, so a change to one implementation forks it from the original, or both from both. That kind of re-use does not sounds as relevant to your scenario.
Nevertheless, I tend to the look at script transformations (for the most part) as proof of concept for custom transforms. If and once they are converted over to Custom transforms, then there is no more cut-and-paste inheritence (i.e. you can fix bugs once, not N times) Without taking advantage of the two re-usable objects (packages and custom components), forking will likely occur.|||
jaegd wrote:
The bullet to bite is to use sub-packages more frequently. If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)
Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?
That said, it sounds like a custom transform is probably the way to go.|||
jaegd wrote: The bullet to bite is to use sub-packages more frequently. If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)
Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?
That said, it sounds like a custom transform is probably the way to go.
Correct. The only easy way to "send" to a subpackage is via staging tables (either database tables or flat files)
Subscribe to:
Posts (Atom)