Error using Dtexec with DTS Package

abollini71 1 Reputation point
2022-03-09T09:51:02.237+00:00

I'm copying a SQL Server database to another (my_database to my_database_copy) in the same instance.

I'm using a DTS Package with the "SQL Server Agent". I scheduled the data transfert and it works regularly, but I need to use the Dtexec to launch the data trasfer as a user command.

I copied the command line:
/SQL "\"SQL\DTS Packages\Copy Database Wizard Packages\my_package\"" /SERVER SQL /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING E

and i use it in the Prompt window:
C:\Windows\system32>DTEXEC /SQL "\"SQL\DTS Packages\Copy Database Wizard Packages\my_package\"" /SERVER SQL /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING E

The script start to works, I thinks it create locally the database but an error occurs during the data transfer, here is the error log:

-- =================================================
-- NON_TRANSACTABLE

CREATE DATABASE [my_database_copy]
CONTAINMENT = NONE

[sql script for dabase structure]

-- =================================================
-- COMPENSATION

USE [master]
GO
DROP DATABASE IF EXISTS [my_database_copy]
GO

> Utilit… di esecuzione pacchetti Microsoft (R) SQL Server
Version 15.0.2000.5 for 64-bit
Copyright (C) 2019 Microsoft. Tutti i diritti sono riservati.

Start: 09:01:54
Error: 2022-03-09 09:02:08.89
Code: 0x00000000
Origin: SQL_SQL_Transfer Objects Task
Description: An error occurred during the data transfer. For details, see the internal exception.
StackTrace: in Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
in Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
InnerException-->This login already has an account with a different user name associated.
StackTrace: in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) in System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
in System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
in Microsoft.SqlServer.Management.Smo.Transfer.ExecuteStatements(SqlConnection destinationConnection, IEnumerable`1 statements, SqlTransaction transaction)
in Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
End error
DTExec: Package execution completed. DTSER_FAILURE (1).

May be, the script, doesn't running as "SQL Server Agent"? If yes, how can I do it? Can you see other problems?

Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-03-09T12:59:03.777+00:00

    The error is:
    This login already has an account with a different user name associated.

    You need to fix that problem if you want to use transfer logins.

    0 comments No comments

  2. ZoeHui-MSFT 33,626 Reputation points
    2022-03-10T06:55:41.617+00:00

    Hi @abollini71 ,

    This login already has an account with a different user name associated.

    It seems that you are using SQL Transfer Objects Task and the error thrown when transferring the logins.

    You may set copysqlserverlogins to false cause you are coping the database to the same instance.

    To copy the database, you may also use Transfer Database Task.

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments