ETL Developer Notes (すべてがデータです): Upgrading old code part 1

I have been looking into some old legacy code that I build back in 2005, on this project we where trying to create an archive process to move data around according to specific queries and column filters.
The task is a little tedious,but perseverance and patients is the key. All the original code was wrote using VB .net and now I am translating into C# using VS 2012. Its amazing on how this process still been used for the last 5+ years, but I guess someone has to do the update, Finally this would help me on see back on time how better or worse my coding has improved.
The first step is to transfer all the variables from one package to the other as a lazy coder I hate having to type each variable, but I could just straight into the xml code and extract the block of code from the original ssiss to insert into a brand new empty package.

<DTS:Variables>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{017F488B-CAB0-4E2F-A28C-AA0E72EF1905}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsDropDestination">
<DTS:VariableValue
DTS:DataType="11">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{C57F09EB-BDAC-4044-AF7F-72F416AE7A2A}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsFastLoad">
<DTS:VariableValue
DTS:DataType="11">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{990E3128-0F9A-4CC9-AEE3-47C9F20CD0C9}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsRetention">
<DTS:VariableValue
DTS:DataType="11">-1</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{F3860C9C-9ED1-435F-BBE1-5FCF4DAE238E}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsSqlCmd">
<DTS:VariableValue
DTS:DataType="11">-1</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{D967607B-6FB8-4D54-B8EE-06FD3A249156}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsSqlOverride">
<DTS:VariableValue
DTS:DataType="11">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{9269E932-E21F-4E22-8B4B-EB6368830FE9}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="bit_IsTransferData">
<DTS:VariableValue
DTS:DataType="11">-1</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{7C389460-CDE6-4F0E-BB0D-F98F7C65D438}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_ArchiveExportId">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{1818DC80-D176-4F1C-9853-010A019975CE}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_ArchiveLogId">
<DTS:VariableValue
DTS:DataType="3">-1</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{A5171CC1-3977-43A7-AC09-1F9A1D2956D9}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_deleteStatus">
<DTS:VariableValue
DTS:DataType="3">7</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{B0E34C5C-7CE8-4115-BB54-F65451F6C9BC}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_DestCurrCnt">
<DTS:VariableValue
DTS:DataType="3">-999</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{F1014B6C-838E-45B7-8D2F-8B6D1514E51F}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_DestPrevCnt">
<DTS:VariableValue
DTS:DataType="3">-999</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{36698DF5-2832-4D09-B21C-5AD53CA0A925}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_FastLoadBatchSize">
<DTS:VariableValue
DTS:DataType="3">10000</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{8C6235C1-DB92-461D-A125-E23641704A6C}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_FastLoadCommit">
<DTS:VariableValue
DTS:DataType="3">50000</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{60B7481B-D459-47D1-A2B6-1F4E61946088}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_GroupId">
<DTS:VariableValue
DTS:DataType="3">2</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{33B5DF71-CD1C-4F54-832E-01E6BE3EF210}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_IsPkgFail">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{BD218A66-22DB-4D68-A8F1-8D5F995CBD1E}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_IsValidDestination">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{73FF4DD8-7FEF-444E-92A5-3E9E840A14A4}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_IsValidSource">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{A504BE43-075F-4177-B71B-7FE9D75ECF97}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_Retention">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{8353B03D-5BAB-44DF-A768-33272C29FD8E}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_SourceCnt">
<DTS:VariableValue
DTS:DataType="3">-999</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{1DD187AB-E5B4-444F-9AA7-D1D2BDC76DF3}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="int_status">
<DTS:VariableValue
DTS:DataType="3">0</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{A2883D87-84BB-4CBB-93A0-1D5E4E237837}"
DTS:IncludeInDebugDump="6789"
DTS:Namespace="User"
DTS:ObjectName="obj_ArchiveList">
<DTS:VariableValue
DTS:DataSubType="ManagedSerializable"
DTS:DataType="13">
<SOAP-ENV:Envelope xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<xsd:anyType
id="ref-1"></xsd:anyType>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{C555DF9D-BDC4-484E-9AA9-9BDD70E70B82}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_AutoSqlCmd">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{93EDD972-9341-4D3D-B2C9-0BC8D5B7420A}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_BatchId">
<DTS:VariableValue
DTS:DataType="8">20111107111825</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{CAEDA2DB-311D-4C75-9EE6-2D037A3263D7}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_ComponentName">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{300F3029-A4E1-44E5-9077-30985571BE8A}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_DestinationDatabase">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{E8469E4A-79CF-4FA5-A0FF-1350DE9B84B0}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_DestinationSchema">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{5961BA4B-4E6D-4496-A3D9-5264387D8764}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_DestinationServer">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{58D49509-95CD-4C4C-BAE8-704F25AA7C7C}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_DestinationTable">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{CA481EF7-6815-4DBC-B295-5DE0C657C313}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_ExcludeColumns">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{1531B578-D9FC-4A3F-B30E-69C8C8BF740F}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_PkColumn">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{64C35DAB-9517-4301-AB13-D8811C9C819A}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_RetentionColumn">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{27E9C751-1E76-445C-AA25-0E5A9C100F1B}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SourceArchiveTable">
<DTS:VariableValue
DTS:DataType="8">[dbo].[tblSourceArchived]</DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{B1EA3528-0652-4022-AA0E-896165D47DC7}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SourceDatabase">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{C1DAC597-793B-4287-90F0-939128F08AC0}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SourceSchema">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{47537672-EBAD-44A3-BD2C-0988EFE1247C}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SourceServer">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{6D44E69F-94C1-4354-96C1-F4F56CABBC10}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SourceTable">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{9658F4CD-EA2D-453C-B9A8-E5B95897A599}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SqlCmd">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{FF143846-8207-461C-BBCD-4215C81CDFD7}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_SqlFilter">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
<DTS:Variable
DTS:CreationName=""
DTS:DTSID="{1659F4CD-0CBB-40A3-A075-FF82C5677EF2}"
DTS:IncludeInDebugDump="2345"
DTS:Namespace="User"
DTS:ObjectName="str_XmlDoc">
<DTS:VariableValue
DTS:DataType="8"
xml:space="preserve"></DTS:VariableValue>
</DTS:Variable>
</DTS:Variables>
<DTS:Executables>
<DTS:Executable
DTS:refId="Package\ArchiveData"
DTS:CreationName="STOCK:SEQUENCE"
DTS:Description="Sequence Container"
DTS:DTSID="{59A61183-9C4C-4CEE-BF0D-516AFBD1005E}"
DTS:ExecutableType="STOCK:SEQUENCE"
DTS:LocaleID="-1"
DTS:ObjectName="ArchiveData">
<DTS:Variables />
<DTS:Executables>
<DTS:Executable
DTS:refId="Package\ArchiveData\ArchiveTable"
DTS:CreationName="STOCK:SEQUENCE"
DTS:Description="Sequence Container"
DTS:DTSID="{D4B1DA24-994A-48DE-B0F0-D4548B029B1E}"
DTS:ExecutableType="STOCK:SEQUENCE"
DTS:LocaleID="-1"
DTS:ObjectName="ArchiveTable">
<DTS:Variables />

ETL Developer Notes (すべてがデータです)

September 20, 2013

Upgrading old code part 1

No comments:

Post a Comment

Contact Form