.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Is there any way to get the Transfer SQL Server Objects Task to not throw error if an object already

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

I've asked this before but never got an answer. Is there a way to configure the Transfer SQL Server Objects Task so that it will only transfer objects that don't already exist in the destination? Or to skip over objects that already exist?

I do not want to "roll my own". I want to use the task in order to save time.

View Complete Post

More Related Resource Links

Why does BI "Transfer SQL Server Objects Task" error occur?


I'm using SSIS to copy all tables and the data from server1 to server2.  Database names are same on both source and destination servers. dbo.MyTable definately exists in the source so I don't understand this error message:

 [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'dbo.MyTable'.".  helpFile=dtsmsg100.rll helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

 There's nothing fancy about MyTable:

CREATE TABLE [dbo].[MyTable](

[MyId] [

Transfer SQL Server Objects Task

I'm trying to use the Transfer SQL Server Objects Task to copy database users and database roles from one database to another. The problem is that some of the users already exists in the destination database. Is there a setting or expression or error handler that will allow me to specify to only copy the objects that don't already exist? I can ignore the failure but I won't know if it's really a copy failure or a duplicate. I read the roll-your-own blog referenced in a similar post (http://blogs.msdn.com/b/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx) but I don't know if a property exists for the transfer object with will allow me to indicate that I want to copy users that aren't already in the destination. Has anyone successfully done this? It seems like it would be a simple task.

Does Transfer SQL Server Objects task transfer objects created in the source AFTER the package has b

I created an SSIS package which contains a Transfer SQL Server Objects Task. I configured this task to copy table objects, stored procedures, and object permissions to the destination. Between the time I created my SSIS package and the time it was run, someone created a new table object in the source, and changed permissions on a stored proc in the source. My question is this, at the time the SSIS package is created, behind the scenes, does SSIS create a list of objects to transfer? I had hoped that it creates the list of what specific objects (of the pre-defined type) to transfer at runtime so that whatever changes were made to the source database would be included at runtime.

"Transfer SQL Server Objects Task" doesn't always copy the data when it's configured to do so

Can someone enlighten me on why this is happening? I have three instances of the "Transfer SQL Server Objects Task" in an SSIS package, each instance copies tables and data from a different source database. Two of the three copy the data without a problem, but one of them does not copy the data even though I have set the CopyData property = true.  And it does not give an error. The status at the end reports Success. Why is this happening?

SQL Server and DMO: Distributed Management Objects Enable Easy Task Automation


SQL Server can be administered programmatically using system stored procedures, but Distributed Management Objects (DMO) offer a more modern, object-oriented alternative. This article introduces SQL-DMO in SQL Server 7.0 and SQL Server 2000 and describes the SQL-DMO object model, then focuses primarily on the Databases tree and the JobServer tree of the object model. The sample code and the article show how to use various objects such as the Registry object, the Configuration object, and the Database object to automate common administration tasks such as programmatically retrieving configuration settings, creating new databases, applying T-SQL scripts, and creating and scheduling backups.

Francesco Balena

MSDN Magazine May 2001

Error 2908 while installing sql server 2005 management objects collection on Win 7

I have SQL Server native client 2005 installed on Win 7 64-bit machine. While installing SQL Server XMO 2005 it gives error 2908 and aborts installation. Any suggestion on the error?

Error: 0xC002F304 at FTP Task, FTP Task: An error occurred with the following error message: "Object

Hi All, I'm trying the FTP Task, I configure it as follow The FTP connection manager is ftp.microsoft.com The port is 21 The credentials anonymous Retries 5 and the chunk one The time out 60 I tested the connection and I does very well     In General section I set the FTPConnection to the connection manager In the File Transfer section IsLocalVariable is set to true And I defined an FTP varriable scope that holds the path to c:\temp\FTPDestination  I set the OverrideFileAtDest to true I set the Operation to recieve files  I set IsRemoteVariable to false I set Remote path to / The task seems to be well configured  but this runtime error raises once the package is fired Error: 0xC002F304 at FTP Task, FTP Task: An error occurred with the following error message: "Object reference not set to an instance of an object." So what can I do to deal with this situation? Thank you The complexity resides in the simplicity

Transfer SQL Server Objects reporting success but nothing transferred

I'm trying to "clone" the structure of one database to a new blank database. I can copy the tables over using the Transfer Sql server object without any problems. When I create a separate task "Transfer Sql server object" for the views (CopyAllViews set to true or picking individual views), it always reports success however none of the views were copied. The same problem occurs when I attempt to copy stored procedures. Anyone have an idea of what's going wrong?  

Unable to install SQL Server 2005 Express: The error is (-2146885628) Cannot find object or propert

I am installing 2 instances of SQL Server 2005 Express for 2 different accounting packages on Windows XP SP3.  I have the first installed, after much use of the forums.  The version of setup.exe was 2005.90.2047.0. I am not able to install the second, for Microsoft Office Accounting 2009, setup.exe version 2005.90.3042.0.  The error I am getting in Summary.txt is: Error String    : The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually." The error is  (-2146885628) Cannot find object or property. Error Number    : 29503 The errorlog file for MSSQL.2 has a lengthier description: 2009-09-05 23:00:32.13 Server      Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)     Feb  9 2007 22:47:07     Copyright (c) 1988-2005 Microsoft Corporation     Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3) 2009-09-05 23:00:32.31 Server      (c) 2005 Microsoft Corporation. 2009-09-05 23:00:32.31 Server      All rights reserved. 2009-09-05 23:00:32.32 Server      Server process ID is 5516. 2009-09-05 23:00:32.32 Server      Authentication mode is MIXED. 2009-09-05 23:00:32.35 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG'. 2009-09-05 23:00:32.38 Server      Regi

SQL Server 2005 permissions error. The EXECUTE permission was denied on the object "xp_instance_reg


I have a SQL Server host running SQL 2005 9.00.4294 x86 Standard Edition running on Windows build 2195 SP4.  My client workstation is running only SQL Server 2005 workstaion components.   When a user of the host who has db_owner access attempts to view the properties of a table by right-clicking the table and then clicking Properties, the following error mesage is displayed:  

"The EXECUTE permission was denied on the object "xp_instance_regread", database 'mssqlsystemresource', schema 'sys'

For security purposes, I do not want to grant execute on xp_instance_regread to these users. Does anyone know of a workaround that will allow members of db_owner to access table properties using the abovementioned method and does not require execute access to be granted?




Object Explorer/Server Explorer Error

I have just installed the released VS2005 as well as the released SQL2005.  When ever I try to browse my SQL Server with either the Object Explorer in MS SQL Server Management Studio or the Server Explorer in VS2005 I get the following error:

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Does any one have possible solutions for me? Any help would be greatly appreciated.


throw exception message giving internal server error on live site


using vb.net/asp.net 2005

when a user enters a bad email I am doing a check on this and throwing an exception message as follows, this works fine on the test site but for some reason the same code on the live site gives a "internal server error" (http code 500).  The code below:

            If isThisEmailValid(strEmailThatTheUserEntered) Then
		'do something


                Throw New Exception("You entered a bad email address.")

            End If

not certain why this is happening, I assume that it's some server or config difference between the test and live sites.  has anyone seen this before?  For a quick fix i'm registering javascript alert and showing the same text so it works but I would like to figure out why the code above is not working.

as always, thanks for your feedback


transfer database task error



I have created a SSIS package that does nothing more than loop through all DBs and copies the userDBs to another server. However, I keep getting an error after the task has created the database during its execution of "Create Role" statements. Here is the error:

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [aspnet_WebEvent_FullAccess] " failed with the following error: "User, group, or role 'aspnet_WebEvent_FullAccess' already exists in the current 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}). The Execute method must succeed, and indicate the result using an "out" parameter.


Now it appears to me that the Transfer DB task keeps using master as the current database even after it has created the new DB? Why would it does this when at the source the database role is under the usersDB?



Error When Joining Reporting Server to Farm: "Task configdb has failed with an unknown exception" an


This is apparently a very common error message, as I see numerous posts about it on these forums.  Unfortunately, none of the solutions have worked for me.  So I'm hoping somebody knows what is happening in my case.

CURRENT FARM (all in same domain):

Server Web3, Windows Server 2003 R2 (x86) <- WFE, WSS 3.0, SP2
Server Web4, Windows Server 2003 R2 (x86) <- WFE, WSS 3.0, SP2 (also Central Admin host)
Server SQL2, Windows Server 2003 R2 (x64) <- Database server for WSS installation (SQL 2008)
Server SQL5, Windows Server 2008 R2 (x64) <- New reporting server (SQL 2008), WSS 3.0 SP2 - unjoined from farm

- Reporting server database is configured in Sharepoint integrated mode.
- All WSS servers are at SP2.  I have double, triple, and quadruple checked this.  Build on all three servers is 12.0.6425.1000 (as retrieved from Add/Remove Programs and Programs and Features, respectively.)
- No additional hotfixes were applied after SP2.

When trying to join my new server (SQL5) to the existing farm using the configuration wizard, I get the following error...

10/11/2010 19:21:16  8  ERR                      Task configdb has failed with an unknown exception

SSIS Database Transfer Error - "Role Exists" even though DB is being overwritten in task.



Can't get over this error, and net searches reveal other postings similiar, but no answers.

SSIS database transfer task (with overwrite) from SQL 2k source to SQL 2k5 destination fails with:


Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "CREATE ROLE [RFRSH_USER] " failed with the following error: "User, group, or role 'RFRSH_USER' already exists in the current 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}). The Execute method must succeed, and indicate the result using an "out" parameter.

The error seems the same regardless if the destination DB exists or not!

Anyone have a solution?




"TableList" property of "Transfer SQL Server" task


Hi All,

We are using "Transfer SQL Server" task into our SSIS package, previously we were knowing which actually tables/objects we were using inside our SSIS and hence we were hard-coding by selecting tables/objects from the "TableList" collections (...).

But now we want to make the package dynamic as our business rule and requirement got change, and hence we have changed packages logic and now we will be passing objects/table-name(s) by package variable to this TableList property through an expression, but package is getting failed after we change the package by using the TableList property in expression.

So can anybody help me out, how to use TableList property dynamically?




transfer sql server objects - table list made dynamic


i've seen several links when searching for this problem but nothing definitely solves this...or if it does it seems to be among people who seem to know scripting really well and can figure stuff out. my problem is as follows.

i have a remote server that i connect to (need credentials and cannot use windows authentication), and from this box i need to extract certain tables from certain databases. i tried a linked server and its ridiculously slow. the other method was to build it via ssis (which needs a mapping structure for each table and cannot be made dynamic). lastly i came across this. this works really well, but i'm amazed that I cant use a variable for the table name. I've tried hardcoding the name, using a string and object variables but no go.

so i see that people have created a custom transfer objects with vb.net and c#. can someone please give me the layman's code block to enter into the script task? i've tried following instructions and end up with errors. As i paste the code some segments remain underlined and when you hover your mouse you see tooltips like Type StringCollection not defined. So maybe i'm missing some libraries or dll's that are supposed to go in and i have no clue which ones. i really like using the expressions and have iterated object variables and simple variables alike and its ridiculous that this is has room to be con

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend