Bug with 'enable identity insert on'

I'm using sp1, tryng to copy from 2005 db to 2005 db, selecting many tables at once, delete target rows on, identity insert ON.

choosing not to optimize for many tables:

Error: 0xC0202009 at Data Flow Task, Destination - table1 [22]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'dbo.table1.".

Error: 0xC0202009 at Data Flow Task, Destination 3 - table2 [226]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

Etc., for each table

Optimizing for many tables:

Error: 0xC0202009 at {AB2253E6-28B4-4D5A-B843-236F8E53E075}, OLE DB Destination [2]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'dbo.table1.".

Information: 0x402090E0 at {AB2253E6-28B4-4D5A-B843-236F8E53E075}, OLE DB Destination [2]: The final commit for the data insertion has ended.

When I drop and recreate the tables manually and set 'identity insert on' it doesn't bring the identity field over AT ALL.

I like that MS has tried to make it easier to move many tables at once by allowing us to set 'enable identity insert' to everything at once, but it doesn't appear to work. ( I've been puzzling over how it could work, anyway...)

Before sp 1, I had to go into each table by clicking the 'edit' button, and it would bring the correct identity value over, but the tables would no longer have the identity property on that field.

I have also tried 'run as a transaction' to no avail...

Well, the error is about PK violation, not about the Identity column definition.

|||Unless the identity column is the PK, in which case you should not have any columns mapped to the identity field in the destination OLE DB component. Leave it blank.|||

The identity column is indeed the PK - NOTE: I didn't design this.

Because the identity column is the PK, and various tables reference the PK, I need to be able to import the actual value.

I did tell it to delete the data first.

If I drop the tables and re-create them with no data, it doesn't bring the identity value over at all, the field auto-numbers.

I seem to be in a no-win situation. Even if I drop all the keys, i still have the problem with not getting the actual identity value inserted.

If the tool is going to allow you to indicate the data should be cleared in the tables first, I think there should be some indication regarding the keys.

I suppose my problem actually lies with the identity value not coming over. I have searched in this forum and found that several others seem to be having the same problem, but have not seen a resolution.

?

|||Are you using "fast load" in the OLE DB destination with "Keep identity" checked?|||

Perhaps I'm misunderstanding the problem but I have just managed to populate a table with an identity column as the PK by loading values into that column.

Here's the code to set it up:

Code Snippet

CREATE DATABASE tmp

GO

USE tmp

GO

CREATE TABLE source (id INT IDENTITY(1,1) PRIMARY key, chr CHAR(1))

GO

INSERT source VALUES ('a')

INSERT source VALUES ('b')

INSERT source VALUES ('c')

CREATE TABLE destination (id INT IDENTITY(1,1) PRIMARY key, chr CHAR(1))

GO

and below is the package that realised it. Let me know what is different in your scenario.

-Jamie

Code Snippet

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</< FONT>DTS:Property><DTS:Property DTS:Name="VersionComments"></< FONT>DTS:Property><DTS:Property DTS:Name="CreatorName">CT\jtbx</< FONT>DTS:Property><DTS:Property DTS:Name="CreatorComputerName">2UA63600NJ</< FONT>DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">3/29/2007 3:25:55 PM</< FONT>DTS:Property><DTS:Property DTS:Name="PackageType">5</< FONT>DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</< FONT>DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</< FONT>DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</< FONT>DTS:Property><DTS:Property DTS:Name="VersionMajor">1</< FONT>DTS:Property><DTS:Property DTS:Name="VersionMinor">0</< FONT>DTS:Property><DTS:Property DTS:Name="VersionBuild">1</< FONT>DTS:Property><DTS:Property DTS:Name="VersionGUID">{2B4A9733-B18D-47EE-A743-7219941EEEB7}</< FONT>DTS:Property><DTS:Property DTS:Name="EnableConfig">0</< FONT>DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></< FONT>DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</< FONT>DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</< FONT>DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</< FONT>DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">LocalHost</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{711EB6A5-1873-451F-AF3A-8453EC797462}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</< FONT>DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</< FONT>DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=.;Initial Catalog=tmp;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</< FONT>DTS:Property></< FONT>DTS:ConnectionManager></< FONT>DTS:ObjectData></< FONT>DTS:ConnectionManager>

<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: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="20717" y="17568" 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="01010000900180380100065461686f6d61" />

</font>

<mouseicon>

<ddsxmlobjectstreamwrapper binary="6c74000000000000" />

</mouseicon>

</diagram>

<layoutmanager>

<ddsxmlobj />

</layoutmanager>

<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Validation warnings occurred." left="0" top="0" 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="{D1234F40-926A-4975-86F0-EFC71316CEB6}" vartype="8" />

<property name="ShowConnectorSource" value="0" vartype="2" />

</ddsxmlobj>

</layoutobject>

<shape groupshapeid="0" groupnode="0" />

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{F740740D-718C-47DB-9D17-E2071E8A5DF3}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost 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:DtsDataFlowDiagram><dwd:BoundingTop>3863</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="7" 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="18071" y="16642" 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="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">

<font>

<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />

</font>

<mouseicon>

<ddsxmlobjectstreamwrapper binary="6c74000000000000" />

</mouseicon>

</diagram>

<layoutmanager>

<ddsxmlobj />

</layoutmanager>

<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11298" top="3863" logicalid="4" controlid="4" 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="{D1234F40-926A-4975-86F0-EFC71316CEB6}/components/1" vartype="8" />

<property name="ShowConnectorSource" value="0" vartype="2" />

</ddsxmlobj>

</layoutobject>

<shape groupshapeid="0" groupnode="0" />

</ddscontrol>

<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Destination" left="12700" top="6509" logicalid="5" controlid="5" 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="{D1234F40-926A-4975-86F0-EFC71316CEB6}/components/52" vartype="8" />

<property name="ShowConnectorSource" value="0" vartype="2" />

</ddsxmlobj>

</layoutobject>

<shape groupshapeid="0" groupnode="0" />

</ddscontrol>

<ddscontrol controlprogid="MSDDS.Polyline" left="12698" top="4628" logicalid="6" controlid="6" masterid="0" hint1="0" hint2="0" width="2201" height="2381" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">

<control>

<ddsxmlobj>

<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />

</ddsxmlobj>

</control>

<layoutobject>

<ddsxmlobj>

<property name="LogicalObject" value="{D1234F40-926A-4975-86F0-EFC71316CEB6}/paths/69" vartype="8" />

<property name="Virtual" value="0" vartype="11" />

<property name="VisibleAP" value="0" vartype="3" />

</ddsxmlobj>

</layoutobject>

<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="5" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">

<point x="13097" y="5027" />

<point x="13097" y="5768" />

<point x="14499" y="5768" />

<point x="14499" y="6509" />

</connector>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">52 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">52 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{59998195-8E74-4AB7-9A98-206CCB534639}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath 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:DestinationName>OLE DB Destination Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}-69</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{CF4C1E95-3336-4ED4-A60C-F4219105D5DE}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</< FONT>DTS:Property><DTS:Property DTS:Name="Disabled">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</< FONT>DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</< FONT>DTS:Property><DTS:Property DTS:Name="LocaleID">1033</< FONT>DTS:Property><DTS:Property DTS:Name="TransactionOption">1</< FONT>DTS:Property><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</< FONT>DTS:Property><DTS:Property DTS:Name="FilterKind">1</< FONT>DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></< FONT>DTS:Property></< FONT>DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"><DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></< FONT>DTS:Property><DTS:Property DTS:Name="TaskContact"></< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</< FONT>DTS:Property><DTS:Property DTS:Name="Disabled">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</< FONT>DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</< FONT>DTS:Property><DTS:Property DTS:Name="LocaleID">-1</< FONT>DTS:Property><DTS:Property DTS:Name="TransactionOption">1</< FONT>DTS:Property><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</< FONT>DTS:Property><DTS:Property DTS:Name="FilterKind">1</< FONT>DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></< FONT>DTS:Property></< FONT>DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{D1234F40-926A-4975-86F0-EFC71316CEB6}</< FONT>DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}</< FONT>DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[source]</< FONT>property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></< FONT>property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</< FONT>property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property></< FONT>properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{711EB6A5-1873-451F-AF3A-8453EC797462}"/></< FONT>connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="47" name="id" description="" lineageId="47" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="46"/>

<outputColumn id="50" name="chr" description="" lineageId="50" precision="0" scale="0" length="1" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="49"/></< FONT>outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="46" name="id" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>

<externalMetadataColumn id="49" name="chr" description="" precision="0" scale="0" length="1" dataType="str" codePage="1252"/></< FONT>externalMetadataColumns></< FONT>output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="48" name="id" description="" lineageId="48" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="51" name="chr" description="" lineageId="51" precision="0" scale="0" length="1" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></< FONT>outputColumns><externalMetadataColumns isUsed="False"/></< FONT>output>

</< FONT>outputs>

</< FONT>component>

<component id="52" name="OLE DB Destination" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">

<properties>

<property id="53" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>

<property id="54" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[destination]</< FONT>property>

<property id="55" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>

<property id="56" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></< FONT>property>

<property id="57" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</< FONT>property>

<property id="58" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>

<property id="59" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</< FONT>property>

<property id="61" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</< FONT>property>

<property id="62" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destinaton. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>

<property id="63" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</< FONT>property>

<property id="64" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property></< FONT>properties>

<connections>

<connection id="60" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{711EB6A5-1873-451F-AF3A-8453EC797462}"/></< FONT>connections>

<inputs>

<input id="65" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns>

<inputColumn id="89" name="" description="" lineageId="47" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="87"/>

<inputColumn id="90" name="" description="" lineageId="50" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="88"/>

</< FONT>inputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="87" name="id" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>

<externalMetadataColumn id="88" name="chr" description="" precision="0" scale="0" length="1" dataType="str" codePage="1252"/></< FONT>externalMetadataColumns></< FONT>input>

</< FONT>inputs>

<outputs>

<output id="66" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="65" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="67" name="ErrorCode" description="" lineageId="67" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="68" name="ErrorColumn" description="" lineageId="68" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></< FONT>outputColumns><externalMetadataColumns isUsed="False"/></< FONT>output>

</< FONT>outputs>

</< FONT>component>

</< FONT>components>

<paths>

<path id="69" name="OLE DB Source Output" description="" startId="11" endId="65"/>

</< FONT>paths></< FONT>pipeline></< FONT>DTS:ObjectData></< FONT>DTS:Executable><DTS:Property DTS:Name="ObjectName">Package9</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</< FONT>DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property></< FONT>DTS:Executable>