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

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

Transfer SQL Server Objects Task

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
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.

View Complete Post

More Related Resource Links

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.

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


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.

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" 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

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?  

"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

HttpContext.Items and Server.Transfer/Execute

The problem was basically that when a Server.Transfer call was applied the script container (and also various ClientScriptProxy script embedding routines) would potentially fail to load up the specified scripts.

Cutting Edge: Pros and Cons of Data Transfer Objects


After a brief refresher on procedural and object based patterns for organizing the business logic layer, the author focuses on data transfer objects and the impact they have on the development of the software project.

Dino Esposito

MSDN Magazine August 2009

Data Points: SQL Server Management Objects


SQL Server Management Objects offer developers a robust toolset for backing up and restoring databases, and issuing DDL commands, as John Papa explains.

John Papa

MSDN Magazine June 2007

Educational question: How server objects are accessed from multiple client threads?

I will appreciate it if someone can help me to undestand how the following scenario works:   There is remoting server that is registered as WellKnownObjectMode.Singleton. Server implementation: It instantiates a class TestServer that implements interface IFoo interface IFoo { IHelper GetHelper() }   interface IHelper { void StoreString(); }   Implementation of TestServer: internal sealed class TestServer: System.MarshalByRefObject, IFoo { private IHelper> helper = new Helper(); public IHelper GetHelper(string helper){return helper;} }   Implementation of Helper: internal sealed class Helper : System.MarshalByRefObject, IHelper { public void StoreString (string val) {}; }   Implementation of a client (simplified; shown to illustrate the question only): static class Program { public static IFoo TestService; public static IHelper Helper; public TestThreads testmultiplecalls; [STAThread] static void Main() { TestService = (IFoo)Activator.GetObject(typeof(IFoo),"ipc://Channel"); Helper = TestService.GetHelper(); testmultiplecalls.Start(); } }   internal class TestThreads { public Start() { for (int i = 0; i < 20; i++) ThreadPool.QueueUserWorkItem((new TestCall(i.ToString())).DoWork); } }     internal class TestCall { private string m_str; public TestCall (string str) {m_str = str;} public void DoWork() { Program.Helper.StoreString(m_str); } }     Que

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?

Best way of storing Serialized objects into Sql server

For some reason, my app need to store serialized objects into SQL Server. Can you guys please tell me the pros and cons and what's the best way of doing it.

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.

What is the difference between Server.Transfer & response.Redirect()

Hello Friends,This is the very favourate question available on every interview questions site.I have searched for it. But didnt get the satisfactory practical difference between these two.Please show me the difference between this two through a working sample demo.Please ilustrate it through a simple sample.

"Server Objects and Events" missing

Using in-line code in VS 2010 RC and I can't get the "server objects and Events" drop down to show up.Not sure if this is a bug or what...in VS 2005 / 2008 it would just appear at the top of in-line code files.  I've checked the options/settings menus and haven't found any way to enable it.
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