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>

Bug Report: Expressions not deleted

I've noticed that if you create a data flow task (such as a DataReader source) and then bind an expression to it, if you rename or delete the task the expressions bound to the task will disappear but still be in the DTSX itself. So, if your expressions stop parsing (if you remove a variable, etc.) you will get errors for these expressions which you thought should have been removed. The workaround is to re-add the tasks with the old names and go delete the expressions from the expression list.
I suggest you raise a bug at http://lab.msdn.microsoft.com/ProductFeedback|||I could reproduce this problem and filed a bug.

Browser Role - Deletes

I'm trying to restrict my users from being able to delete or move any of the
reports. Using the Browser Role ( View Folders, View Reports ) still
allows them to get to the "Show Details" page with full access to delete my
reports. What am I missing?
TIA
TinaThe Show Details will still show the check box to delete the report. But
the user does not have permissions to do it and will receive an error. If
this is not the case, please let me know.
Make sure you apply permissions at the folder level as well.
| From: "Tina Smith" <tb.smith@.earthlink.net>
| Subject: Browser Role - Deletes
| Date: Tue, 18 Jan 2005 11:31:20 -0500
| Lines: 9
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <O1QclsX$EHA.2076@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: user-10ibco9.biz.mindspring.com 65.37.179.9
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:40126
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm trying to restrict my users from being able to delete or move any of
the
| reports. Using the Browser Role ( View Folders, View Reports ) still
| allows them to get to the "Show Details" page with full access to delete
my
| reports. What am I missing?
|
| TIA
| Tina
|
|
||||Yes, they do receive an error stating they don't have the permissions.
I'd rather not show them the option to delete the reports when they don't
have the permissions to. Is there anyway to eliminate the "ShowDetails"
for the browser role?
Thanks
Tina
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:tR6$y7X$EHA.3360@.cpmsftngxa10.phx.gbl...
> The Show Details will still show the check box to delete the report. But
> the user does not have permissions to do it and will receive an error. If
> this is not the case, please let me know.
> Make sure you apply permissions at the folder level as well.
>
> --
> | From: "Tina Smith" <tb.smith@.earthlink.net>
> | Subject: Browser Role - Deletes
> | Date: Tue, 18 Jan 2005 11:31:20 -0500
> | Lines: 9
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <O1QclsX$EHA.2076@.TK2MSFTNGP15.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: user-10ibco9.biz.mindspring.com 65.37.179.9
> | Path:
>
cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
microsoft.public.sqlserver.reportingsvcs:40126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I'm trying to restrict my users from being able to delete or move any of
> the
> | reports. Using the Browser Role ( View Folders, View Reports ) still
> | allows them to get to the "Show Details" page with full access to delete
> my
> | reports. What am I missing?
> |
> | TIA
> | Tina
> |
> |
> |
>

Break statement does not work

I am trying to delete 500000 rows at a time from a big table. I am trying to
loop through it. I am attaching my code, what am I doing wrong. It either
will delete only 500000 rows and exit ot it will keep on looping. I am askin
g
it to delete data older than a certain date. If there are 3000000 rows , I
want it to loop till it deletes 3000000 rows and then exit. Instead if i set
the loop counter to 10, it will loop ten times and then exit. The break
statement does not work
here is the code
declare @.i int
select @.i=10
set rowcount 1000000
while @.i>0
begin
delete from flat_reporttbl
where logdate<'4/1/2004'
select @.i=@.i-1
break
end
thankscheck it.
delete top(10) from flat_reporttbl
where logdate<'4/1/2004'
the problem in ur code is ,
delete statement deletes all rows
which satisfies the condition in
where clause and
then check the while condition.
"batgirl" <batgirl@.discussions.microsoft.com> wrote in message
news:49793BEE-8D40-480E-8D5A-03E77A45A541@.microsoft.com...
>I am trying to delete 500000 rows at a time from a big table. I am trying
>to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am
> asking
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i
> set
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks|||I don't know if we can use the TOP keyword with delete. It gives me an error
message when I try to use it. Also I have already set the rowcount, so does
using TOP help?
batgirl
"batgirl" wrote:

> I am trying to delete 500000 rows at a time from a big table. I am trying
to
> loop through it. I am attaching my code, what am I doing wrong. It either
> will delete only 500000 rows and exit ot it will keep on looping. I am ask
ing
> it to delete data older than a certain date. If there are 3000000 rows , I
> want it to loop till it deletes 3000000 rows and then exit. Instead if i s
et
> the loop counter to 10, it will loop ten times and then exit. The break
> statement does not work
> here is the code
> declare @.i int
> select @.i=10
> set rowcount 1000000
> while @.i>0
> begin
> delete from flat_reporttbl
> where logdate<'4/1/2004'
> select @.i=@.i-1
> break
> end
> thanks