Showing posts with label automatically. Show all posts
Showing posts with label automatically. Show all posts

Tuesday, March 27, 2012

DTS Fail when IDENTITY(1,1)

Hi,

i m facing this error when running DTS on IDENTITY(1,1) Field.
how can this field increment automatically ?

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum specified. (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Insert error, column 14 ('s_no', DBTYPE_I4), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.) (Microsoft OLE DB Provider for SQL Server (80040e21): Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
Step Error code: 8004206ADon't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP|||there is a option button (check box) in your transformation task that says
"enable identity insert"
change that.|||Does the table have existing data?

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?

Does this import need to update as well as add data (or delete)?|||Hi,
Actually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.

e.g

Table1
mfg_no varchar(255)
Product_Name varchar(255)
Description varchar(255)

Table2
s_no int IDENTITY(1,1) Not Null {constraint for identity seed 1)
mfgno varchar(255)
ProductName varchar(255)

i just have to transfer data column (mfgno and product name) from Table1 to Table 2. the problem is that when i run dts its not inserting "s_no" automatically in Table 2 (i hope SQL server should handle this but its not working). if i remove constraint of identity (s_no)and make it allow Null then data transfer successfully.

i m using DTSDataPump in package to transform data.

waiting for ur reply|||Does the table have existing data?
NO

Does your import file have existing "keys" (boy I hate to use that word here) that need to be imported into that column?
NO there is no existing keys in SourceTable

Does this import need to update as well as add data (or delete)?
No it just Add data|||ctually guys i have not run the DTS wizard to transform data i have written my own dts package that transfer hetrogenous data from one table to another.That is what I thought... Just view the transform code, and remove any transformation for the IDENTITY column. It ought to be pretty simple.

-PatP|||Don't include any tranformation for the IDENTITY column. In other words, have DTS ignore that column and let the database engine deal with it.

-PatP

yes i have't include any transformation for the identity column and i m wonder why db engine not handle this :mad:|||I don't get it...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE Table1(mfg_no varchar(255), Product_Name varchar(255), [Description] varchar(255))
CREATE TABLE Table2(s_no int IDENTITY(1,1) Not Null, mfgno varchar(255), ProductName varchar(255))
GO

INSERT INTO Table1 (mfg_no, Product_Name, [Description])
SELECT 'x002548','Gas Powered Blenders', 'The Ultimate is finally here' UNION ALL
SELECT 'x002548','Shake, rattle and Roll Mixers', 'Get the Party started' UNION ALL
SELECT 'x002548','1.75 litre sampler packages', '6 Bottles of unconciousness' UNION ALL
SELECT 'x002548','Margarita Glasses', 'Glasses, Hell you wont be able to see, son' UNION ALL
SELECT 'x002548','Hide away Car Key Chain', 'inebriation Dector - will hide your keys on you'
GO

SELECT * FROM Table1
GO

INSERT INTO Table2(mfgno, ProductName)
SELECT mfg_no, Product_Name FROM Table1
GO

SELECT * FROM Table2
GO

SET NOCOUNT ON
DROP TABLE table1
DROP TABLE table2
GO|||I don't get it...
[/code]

u run direct quries its working fine :( but if i run dts package then it fails :(|||That's what I don't get.

Why are you bothering with a DTS package at all?|||That's what I don't get.

Why are you bothering with a DTS package at all?

i want to tranfer data from one table to another on schedule basis so that i need a DTS package that tranfer only those column data that i want to tranfer.

whole dts working fine its transform the desired column to anothe table but when identiy column is removed cuz destination table has a unique column type int and identity seed 1.

:( this is sample code u can run it . if u remove constraint on s_no it will work fine. try this

CREATE TABLE [DTS_UE].[dbo].[NorthwindProducts] (
[s_no] [int] IDENTITY(1,1) Not NULL ,
[ProductName] [nvarchar] (40) NULL ,
[CategoryName] [nvarchar] (25) NULL ,
[CompanyName] [nvarchar] (40) NULL )
This is the Visual Basic code for the application:

Public Sub Main()
'Copy Northwind..Products names, categories, suppliers to DTS_UE..NorthwindProducts.
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step2
Dim objTask As DTS.Task
Dim objPumpTask As DTS.DataPumpTask2
Dim objTransform As DTS.Transformation2
Dim objLookUp As DTS.Lookup
Dim objTranScript As DTSPump.DTSTransformScriptProperties2
Dim sVBS As String 'VBScript text

Set objPackage = New DTS.Package
objPackage.FailOnError = True
objPackage.LogFileName = "C:\Temp\TestConcurrent.Log"

'Establish connections to data source and destination.
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 2
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect

'Create copy step and task, link step to task.
Set objStep = objPackage.Steps.New
objStep.Name = "NorthwindProductsStep"
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objPumpTask = objTask.CustomTask
objPumpTask.Name = "NorthwindProductsTask"
objStep.TaskName = objPumpTask.Name
objStep.ExecuteInMainThread = False
objPackage.Steps.Add objStep

'Link copy task to connections.
With objPumpTask
.SourceConnectionID = 1
.SourceSQLStatement = _
"SELECT ProductName, CategoryID, SupplierID " & _
"FROM Northwind..Products"
.DestinationConnectionID = 2
.DestinationObjectName = "[DTS_UE].[dbo].[NorthwindProducts]"
.UseFastLoad = False
.MaximumErrorCount = 99
End With

'Create lookups for supplier and category.
Set objLookUp = objPumpTask.Lookups.New("CategoryLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CategoryName FROM Northwind..Categories " & _
"WHERE CategoryID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp
Set objLookUp = objPumpTask.Lookups.New("SupplierLU")
With objLookUp
.ConnectionID = 1
.Query = "SELECT CompanyName FROM Northwind..Suppliers " & _
"WHERE SupplierID = ? "
.MaxCacheRows = 0
End With
objPumpTask.Lookups.Add objLookUp

'Create and initialize rowcount and completion global variables.
objPackage.GlobalVariables.AddGlobalVariable "Copy Complete", False
objPackage.GlobalVariables.AddGlobalVariable "Rows Copied", 0
objPackage.ExplicitGlobalVariables = True

'Create transform to copy row, signal completion.
Set objTransform = objPumpTask.Transformations. _
New("DTSPump.DataPumpTransformScript")
With objTransform
.Name = "CopyNorthwindProducts"
.TransformPhases = DTSTransformPhase_Transform + _
DTSTransformPhase_OnPumpComplete
Set objTranScript = .TransformServer
End With
With objTranScript
.FunctionEntry = "CopyColumns"
.PumpCompleteFunctionEntry = "PumpComplete"
.Language = "VBScript"
sVBS = "Option Explicit" & vbCrLf
sVBS = sVBS & "Function CopyColumns()" & vbCrLf
sVBS = sVBS & " DTSDestination(""ProductName"") = DTSSource(""ProductName"") " & vbCrLf
sVBS = sVBS & " DTSDestination(""CategoryName"") = DTSLookups(""CategoryLU"").Execute(DTSSource(""CategoryID"")) " & vbCrLf
sVBS = sVBS & " DTSDestination(""CompanyName"") = DTSLookups(""SupplierLU"").Execute(DTSSource(""SupplierID"").Value) " & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Rows Copied"") = CLng(DTSTransformPhaseInfo.CurrentSourceRow)" & vbCrLf
sVBS = sVBS & " CopyColumns = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

sVBS = sVBS & "Function PumpComplete()" & vbCrLf
sVBS = sVBS & " DTSGlobalVariables(""Copy Complete"") = True" & vbCrLf
sVBS = sVBS & " PumpComplete = DTSTransformStat_OK" & vbCrLf
sVBS = sVBS & "End Function" & vbCrLf

.Text = sVBS
End With
objPumpTask.Transformations.Add objTransform
objPackage.Tasks.Add objTask

objPackage.Execute

End Sub

Monday, March 19, 2012

DTS automatic dat file import

Hi y'all

I'm having some trouble with the following script I use in a
dts-package. I use it to automatically import a dat-file of which the
name changes every day. Today the name f.e. is P0000020.dat, tomorrow
it's P0000021.dat and the day after P0000022.dat etc etc The error I
get is end of instruction expected.
This is the script:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Dim oPkg, oDataPump
Dim sSourceTable, sDestinationTable

' Derive the new table names
sSourceTable ="" & GetName
sDestinationTable = sSourceTable
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Set the new values
oDataPump.SourceObjectName = sSourceTable
oDataPump.DestinationObjectName = "[Test].[test].[tblXafaxOutput]"
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

Function GetName
Dim number, counter, file
counter = 1
number = DTSGlobalVariables("NextNumber").Value
number = number + 1
For counter = 1 To 7 - Len(number)
file = file & "O"
Next teller
file = "P" & file & getal & ".dat"
DTSGlobalVariables("NextNumber").Value = number
GetName = file
End Function

I've used this script before in another dts-package where the
file-name needs to be based up-on the date and it worked just fine.
You can find the script at:
http://groups.google.be/groups?hl=n...l e.com#link10

I hope anyone can help me.
thanx in advance
Piedro

PS I've my english is crap it's because it ain't my mothertongue."Piedro" <pproost@.hotmail.com> wrote in message
news:b7e3650a.0309231319.7d8d0ffa@.posting.google.c om...
> Hi y'all
> I'm having some trouble with the following script I use in a
> dts-package. I use it to automatically import a dat-file of which the
> name changes every day. Today the name f.e. is P0000020.dat, tomorrow
> it's P0000021.dat and the day after P0000022.dat etc etc The error I
> get is end of instruction expected.
> This is the script:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim oPkg, oDataPump
> Dim sSourceTable, sDestinationTable
> ' Derive the new table names
> sSourceTable ="" & GetName
> sDestinationTable = sSourceTable
> ' Get reference to the DataPump Task
> Set oPkg = DTSGlobalVariables.Parent
> Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
> ' Set the new values
> oDataPump.SourceObjectName = sSourceTable
> oDataPump.DestinationObjectName = "[Test].[test].[tblXafaxOutput]"
> ' Clean Up
> Set oDataPump = Nothing
> Set oPkg = Nothing
> Main = DTSTaskExecResult_Success
> End Function
> Function GetName
> Dim number, counter, file
> counter = 1
> number = DTSGlobalVariables("NextNumber").Value
> number = number + 1
> For counter = 1 To 7 - Len(number)
> file = file & "O"
> Next teller
> file = "P" & file & getal & ".dat"
> DTSGlobalVariables("NextNumber").Value = number
> GetName = file
> End Function
>
> I've used this script before in another dts-package where the
> file-name needs to be based up-on the date and it worked just fine.
> You can find the script at:
http://groups.google.be/groups?hl=n...l e.com#link10
> I hope anyone can help me.
> thanx in advance
> Piedro
>
> PS I've my english is crap it's because it ain't my mothertongue.

The message you get is probably because of the For .. Next loop. In
VBScript, you can't put the counter variable name after Next (as you would
in VB):

For x = 1 To 10
' Do something
Next ' This is OK

For x = 1 To 10
' Do something
Next x ' This is wrong

Simon