January 11, 2002 at 9:44 am
January 15, 2002 at 6:08 am
I believe you need to check the Enable identity nsert box on the DTS transformation. I know it should be assumed, but it isn't.
Edited by - tpantazi on 01/15/2002 06:08:06 AM
January 20, 2003 at 10:43 am
I am having the same problem. A simple table with an identity column. I define a transform Data task to import a column from one table in a sql 2000 database to a different sql 2000 database on the same server, and receive an "Inert error, column 1 ('priceTieringID',DBTYPE_I4) status 10: Integrity violation; attept ot insert NULL data or data which violates constrants. Unspecified Error"
I have experiemented with a number of the options in the step properties, including having identity insert both checked/unchecked, along with fast load, check constraints, and keep null values both checked/unchecked, nothing appears to work. Any help greatly appreciated.
January 22, 2003 at 9:50 am
That sample row of data from the text file would really help. Are you sure that DTS doesn't think there is more then one column in the import end of things?
Nigel Moore
======================
January 22, 2003 at 12:24 pm
I don't think they are talking DTS folks. I tried and DTS is fine. It is the import/export wizard under tasks that fails on this just like they state. I can recreate the error.
January 22, 2003 at 12:35 pm
I found the reason why is that you cannot specify whether or not the identity column is included in the Import Data task. Because of this it is expecting a value mapped to it. Here is the relavent code when saving as a VB Script both DTS Package and DTS Import/Export Wizard.
DTS Package
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__1"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("Col001", 1)
oColumn.Name = "Col001"
oColumn.Ordinal = 1
oColumn.Flags = 48
oColumn.Size = 2
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("deek", 1)
oColumn.Name = "deek"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 40
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
DTS Import/Export Wizard
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformScript")
oTransformation.Name = "AxScriptXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("Col001", 1)
oColumn.Name = "Col001"
oColumn.Ordinal = 1
oColumn.Flags = 48
oColumn.Size = 2
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Autonumber", 1)
oColumn.Name = "Autonumber"
oColumn.Ordinal = 1
oColumn.Flags = 16
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("deek", 2)
oColumn.Name = "deek"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 40
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation.TransformServerProperties
oTransProps("Text") = "'**********************************************************************" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Visual Basic Transformation Script" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "' Copy each source column to the" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "' destination column" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "'************************************************************************" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "Function Main()" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " DTSDestination(""deek"") = DTSSource(""Col001"")" & vbCrLf
oTransProps("Text") = oTransProps("Text") & " Main = DTSTransformStat_OK" & vbCrLf
oTransProps("Text") = oTransProps("Text") & "End Function"
oTransProps("Language") = "VBScript"
oTransProps("FunctionEntry") = "Main"
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
The code is exactly the same except the Import/Export Wizard still references the Autonumber (my identity) column but does not have a mapping. Due to this it assumes NULL. And unfortunately I don't see a way to prevent the column reference or someway as of yet to tell it to handle increment as normal insert.
January 22, 2003 at 2:56 pm
Strnage when I tried this using the Import wizard on SQL 7.0 (on Wins 98) it worked ok!
Surely you can change things on the 'Column Mappings and Transformations' form 'Transformations' tab anyhow?
Nigel Moore
======================
January 22, 2003 at 3:08 pm
I was doing with SQL 2K on Windows 2000 and XP both give same result. I will have to test with the 7 but don't have it running on an machine here.
Can you give me the steps you used to get your results Nmoore. The only thing was I used the MyID create table from Deeks question.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply