Transfer SQL Server Objects Task not working with Stored Procedures

[Database Administrator]

I'm unable to copy my Stored Procedures from one database to another.
I'm using mixed mode authentication. I have set
CopyAllStoredProcedures to True, DropObjectsFirst to True and
CopySchema to True.



Nothing gets copied. I have followed many web sites that say Transfer
SQL Server Objects Task is broken. Is this true and I should give up?



Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume
is the update for SSIS? yes ?



Thanks for any help



[Transfer SQL Server Objects Task] Error: Execution failed with the
following error: "ERROR : errorCode=-1073548784 description=Executing
the query "CREATE PROCEDURE [dbo].
[del_Admin_RemoveContractorFromContract] @ContractID int,
@ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE
CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE
FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND
CONTRACTOR_ID = @ContractorID " failed with the following error:
"There is already an object named
'del_Admin_RemoveContractorFromContract' in the database.". Possible
failure reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established
correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}".



Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The
Execution method succeeded, but the number of errors raised (1)
reached the maximum allowed (1); resulting in failure. This occurs
when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

[Database Administrator]

On Sep 17, 12:48 pm, "bringmewa...@gmail.com"
wrote:
> I'm unable to copy my Stored Procedures from one database to another.
> I'm using mixed mode authentication. I have set
> CopyAllStoredProcedures to True, DropObjectsFirst to True and
> CopySchema to True.
>
> Nothing gets copied. I have followed many web sites that say Transfer
> SQL Server Objects Task is broken. Is this true and I should give up?
>
> Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume
> is the update for SSIS? yes ?
>
> Thanks for any help
>
> [Transfer SQL Server Objects Task] Error: Execution failed with the
> following error: "ERROR : errorCode=-1073548784 description=Executing
> the query "CREATE PROCEDURE [dbo].
> [del_Admin_RemoveContractorFromContract] @ContractID int,
> @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE
> CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE
> FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND
> CONTRACTOR_ID = @ContractorID " failed with the following error:
> "There is already an object named
> 'del_Admin_RemoveContractorFromContract' in the database.". Possible
> failure reasons: Problems with the query, "ResultSet" property not set
> correctly, parameters not set correctly, or connection not established
> correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}".
>
> Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The
> Execution method succeeded, but the number of errors raised (1)
> reached the maximum allowed (1); resulting in failure. This occurs
> when the number of errors reaches the number specified in
> MaximumErrorCount. Change the MaximumErrorCount or fix the errors.


My preferred way of transferring stored procedures is to open SSMS/
Mgmt Studio and select Programming -> Stored Procedures and open the
Object Detail Browser. Then drag your mouse over all the non-system
stored procedures and right-click and then select 'script as Drop' and
'script as Create.' And execute the resulting scripts in the other
database. Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

[Database Administrator]

But I need these to run on a schedule. Is SSIS a piece of crap or can
it do these basic tasks?

On Sep 17, 10:45 pm, EMartinez wrote:
> On Sep 17, 12:48 pm, "bringmewa...@gmail.com"
> wrote:
>
>
>
> > I'm unable to copy my Stored Procedures from one database to another.
> > I'm using mixed mode authentication. I have set
> > CopyAllStoredProcedures to True, DropObjectsFirst to True and
> > CopySchema to True.
>
> > Nothing gets copied. I have followed many web sites that say Transfer
> > SQL Server Objects Task is broken. Is this true and I should give up?
>
> > Also, I'm on SQL 2005 SP2 which appears to be the latest and I assume
> > is the update for SSIS? yes ?
>
> > Thanks for any help
>
> > [Transfer SQL Server Objects Task] Error: Execution failed with the
> > following error: "ERROR : errorCode=-1073548784 description=Executing
> > the query "CREATE PROCEDURE [dbo].
> > [del_Admin_RemoveContractorFromContract] @ContractID int,
> > @ContractorID int AS DELETE FROM CONTRACTOR_CONTRACTS WHERE
> > CONTRACT_ID = @ContractID AND CONTRACTOR_ID = @ContractorID DELETE
> > FROM CONTRACTOR_USER_CONTRACTS WHERE CONTRACT_ID = @ContractID AND
> > CONTRACTOR_ID = @ContractorID " failed with the following error:
> > "There is already an object named
> > 'del_Admin_RemoveContractorFromContract' in the database.". Possible
> > failure reasons: Problems with the query, "ResultSet" property not set
> > correctly, parameters not set correctly, or connection not established
> > correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> > E9D8-4D23-9739-DA807BCDC2AC}".
>
> > Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The
> > Execution method succeeded, but the number of errors raised (1)
> > reached the maximum allowed (1); resulting in failure. This occurs
> > when the number of errors reaches the number specified in
> > MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
>
> My preferred way of transferring stored procedures is to open SSMS/
> Mgmt Studio and select Programming -> Stored Procedures and open the
> Object Detail Browser. Then drag your mouse over all the non-system
> stored procedures and right-click and then select 'script as Drop' and
> 'script as Create.' And execute the resulting scripts in the other
> database. Hope this helps.
>
> Regards,
>
> Enrique Martinez
> Sr. Software Consultant

[bpeterson]

I was finally able to transfer an sp in .NET script task by overriding the defaults and explicitly setting many of the options. Hope this helps others, as I struggled with it for a couple days.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized
Imports System.Collections.Generic

Public Class ScriptMain

Public Sub Main()

Dim SourceConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
Dim SourceServer As Server
Dim SourceDB As Database

Dim DestinationConnection As Microsoft.SqlServer.Dts.Runtime.ConnectionManager
Dim DestinationServer As Server
Dim DestinationDB As Database

SourceConnection = Dts.Connections("DB_OLEDB")
SourceServer = New Server(CStr(Dts.Connections("DB_OLEDB").Properties("ServerName").GetValue(SourceConnection)))
SourceDB = SourceServer.Databases(CStr(Dts.Connections("DB_OLEDB").Properties("InitialCatalog").GetValue(SourceConnection)))

DestinationConnection = Dts.Connections("Reporting_OLEDB")
DestinationServer = New Server(CStr(Dts.Connections("Reporting_OLEDB").Properties("ServerName").GetValue(DestinationConnection)))
DestinationDB = DestinationServer.Databases(CStr(Dts.Connections("Reporting_OLEDB").Properties("InitialCatalog").GetValue(DestinationConnection)))


Dim SMOTransfer As Transfer
SMOTransfer = New Transfer(SourceDB)

SMOTransfer.CopyData = False
SMOTransfer.CopySchema = True
SMOTransfer.CopyAllObjects = False
SMOTransfer.CopyAllTables = False
SMOTransfer.Options.WithDependencies = False
SMOTransfer.DropDestinationObjectsFirst = True
SMOTransfer.CopyAllUsers = False
SMOTransfer.CopyAllRoles = False
SMOTransfer.CopyAllLogins = False
SMOTransfer.Options.Permissions = False
SMOTransfer.Options.Indexes = False
SMOTransfer.CopyAllDatabaseTriggers = False
SMOTransfer.CopyAllStoredProcedures = False
SMOTransfer.CopyAllUserDefinedFunctions = False
SMOTransfer.CopyAllViews = False

SMOTransfer.DestinationServer = DestinationServer.Name
SMOTransfer.DestinationDatabase = DestinationDB.Name
SMOTransfer.Options.IncludeIfNotExists = True
SMOTransfer.Options.ContinueScriptingOnError = True
SMOTransfer.Options.ExtendedProperties = False

'MsgBox("ObjectType: " + Dts.Variables("ObjectType").Value().ToString())

If Dts.Variables("ObjectType").Value().ToString() = "P " Then
'MsgBox("CopyObject: " + CStr(Dts.Variables("CopyObject").Value) + " " + CStr(Dts.Variables("ObjectType").Value) + "-" + CStr(Dts.Variables("ObjectSourceID").Value))
SMOTransfer.ObjectList.Add(SourceDB.StoredProcedur es(Dts.Variables("CopyObject").Value().ToString()))
SMOTransfer.TransferData()
ElseIf Dts.Variables("ObjectType").Value().ToString() = "X " Then
SMOTransfer.ObjectList.Add(SourceDB.ExtendedStored Procedures(Dts.Variables("CopyObject").Value().ToString()))
SMOTransfer.TransferData()
ElseIf Dts.Variables("ObjectType").Value().ToString() = "V " Then
SMOTransfer.ObjectList.Add(SourceDB.Views(Dts.Vari ables("CopyObject").Value().ToString()))
SMOTransfer.TransferData()
ElseIf Dts.Variables("ObjectType").Value().ToString() = "FN" Or Dts.Variables("ObjectType").Value().ToString() = "TF" Or Dts.Variables("ObjectType").Value().ToString() = "IF" Then
SMOTransfer.ObjectList.Add(SourceDB.UserDefinedFun ctions(Dts.Variables("CopyObject").Value().ToString()))
SMOTransfer.TransferData()
End If


Dts.TaskResult = Dts.Results.Success
End Sub

End Class