My goal is need to synch couple of tables in QA and Prod. I used "Copy SQL Server Objects Task" in DTS package with copy option "replace existing data". It is trying to truncate and insert new data. But BOL is says "Overwrite existing data in the destination objects with the new data from the specified source".
Let me know your thought about this.
Hi,
I am getting an error when doing the above.
I create a new SSIS package, drag in an Execute DTS 2000 Package Task, select and embed a DTS package which consists only one one task (as above), and then change the source & destination details (svr + user/pwd). Then when I go to the Copy tab, I get the following error when I hit Select Objects, to view the objects which the embedded DTS package should copy:
SQL-DMO error 21776: general error.
On further inspection, none of the objects selected for copy within the atomic/original DTS package, remain selected for copy within the embedded DTS package.
I have googled to search for an answer to this one, but to no avail. Any ideas would be greatly welcomed.
Thanks,
Tamim.
I'm making a copy of some tanles between 2 servers.
Server 1 requires a sql login
Server 2 is using Windows Auth.
I have a user on server 1 named "odbc" able to log in.
however my copy task fails, when I drill the error, it's lists the first user in server 1 alphabetically as the failed login???? but in my dts I am specifying the "odbc" user and password.
I think I have a permissions problem on server 1. So my Question, what minimum permissions does user "odbc" need to copy a table?
On server 1 I can copy from northwind to server 2 just fine..but any other db on server 1 causes the weird failure with the wrong username.
Any Ideas? I am not a DBA obviously :)
Thanks,
Carl
I have a SQL2000 database that I need to copy tables from to my SQL2005 database. The table in the 2000 database are owned by a login named tsreader. This login is also in the dbo database role. So, when I access tables I have to prefix tha table name with tsreader. Currently in a SQL2000 database I have DTS packages that pull data over from this database everynight and they work fine. However when I try to do this from SSIS, I get the error "Object does not exist". My assumption is that it is trying to access the database tables as a "select * from tablea" as opposed to "select * from tsreader.tablea".
Any thoughts on how to make this work? I know I have the login correct because it is the same one I use for the SQL 2000 packages. Also, in SSIS it allows me to chose the tables I want yet I still get "Table does not exist at source" when I try and execute it.
Any ideas?
Thanks,
fjk
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
I’m trying to create a DTS Package to copy my sql Server objects to a test Server. The server I’m copying from is UMTS1 and the server I’m copying to is UMTSDEV.
The database name is ProgramSpecs and exists on bother servers. My login is assigned to all server roles on both servers. I have created databases on both servers manually so I’m pretty sure I have all the necessary permissions. I’m using the DTS task ‘Copy Sql Server Objects’ to copy sql server objects and have selected “Drop Destination objects first”.
When I try to execute the package I get the following error:
Error source: MS SQL DMO
Error Description: Invalid OLEVERB Structure [SQL DMO] create file error or UMTS1.ProgramSpecs.LOG
Can anyone tell me what I’m doing wrong?
Thanks
GEM
Copy objects and data between SQL Server databases
"
Display the Select Objects to Transfer dialog box, where you can specify both objects and data to copy, if both the data source and destination are Microsoft® SQL Server™ databases. The objects you can transfer include tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes. You can transfer objects only between multiple instances of SQL Server version 7.0, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and between multiple instances of SQL Server 2000.
"
can I apply "Copy objects and data between SQL Server databases"
to run in two different sqlserver 2000 ( not an instance ) . what I mean is I have two different sql servers located in two different locations( I am not using an instance installation) can I still run the copy and get an identical database in both servers.
Q2. if I have two sql server 2000 with different collations (one is binary and the other is the default) will I be able to run the copy wizard and still have an identical copy of sql server in both servers.
I personally tried to run the copy wizard and IT NEVER WORKED FOR ME and I really do not know the reason.
Thanks for your input.
ali
I'm a wee bit of a newbie concerning DTS and have inherited a db with a DTS containing a Copy SQL Server Objects task set to run nightly. Essentially, it does an informal backup of some core data.
Recently, I was notified that one of the tables it copies over is now empty on the Destination db. The DTS shows that it runs successfully with no errors logged, the table in question IS selected to be copied from the Source database, there IS data in the Source database table, and every other table in the Destination database is populated appropriately.
Any ideas on what would cause this one table to be empty without generating any errors?
FYI, running SQL Server 2000.
I just upgraded to SQLServer 2005 Standard. I do development in adatabase on one server. When a solution is completely tested, I moveit to the operational database on a different server. With SQLServer2000, I could just open the .adp file and do File->Get ExternalData->Import to move the objects. That does not work with 2005.What is the recommended method for moving objects from one database toanother? Obviously, the objects that changed will not be the sameevery time.Thanks!Jer
View 2 Replies View RelatedError code: 0xc0012024
Using "Integration Services Project" template in Business Intelligence Studio. Using platforms Visual Studio 2005 along with SQL Server 2005.
Getting the error while trying to execute package after loading it programmaticaly.
I've just one task "Transfer SQL Server Objects Task" on my Integration Services package. But when I try to execute it from VS 2005 project programmaticaly, it gives the above mentioned error.
The commands I use:
Package pkg = new Package();
pkg = a.LoadPackage(@"C:Documents and SettingsabcMy DocumentsVisual Studio 2005ProjectslSSISSSISPackage.dtsx", null, true);
DTSExecResult dResult = pkg.Execute();
The the error comes like: error: 0xc0012024 The task Transfer SQL Server Objects Task cannot run on this edition of Integration Services. It requires higher level edition.
Please help me.
Thanks in advance,
Bhupesh
Can anyone refer me to good 'recipes' or sources of information on thistopic??I have Visual Studio Tools for Office, which installs SS 2005 Expresslocally to my XP box, and I want to develop in SS 2005, then copy thetables or queries or reports etc. to a SS 2005 Standard server.Thank you, Tom
View 3 Replies View RelatedHello,
I am trying to copy a subset of tables, stored procedures, and views from one database to another database on a named instance of SQL Server 2005. I am attempting to use the Transfer SQL Server Objects task in Integration Services.
I am able to create both Source and Destination connections, and specify the objects I want to transfer. When I run the package, the task turns yellow while it's processing, then green when it completes.
At this point, no objects have been transferred to my destination database. No tables, no procs, no views, nothing. No error has been generated. No information is written to an SSIS log file, other than the fact that objects are being transferred, and then the package is finished.
Does this have something to do with the fact that I'm using a named instance of SQL Server 2005?
Thanks,
Ken
Hi all,
I am using SSIS Transfer SQL Server Objects Task to copy all the objects of a database to another without the data. The properties that i have set for this task is as:
DropObjectsFirst = True
IncludeExtendedProperties = True
CopyData = false
CopySchema = True
UseCollation =True
IncludeDependentObjects = True
CopyAllObject = False
CopyAllTables = True
CopyAllViews = True
CopyAllStoredProcedures = True
CopyAllUserDefinedFunctions = True
CopyAllDefaults = True
CopyAllUserDefinedDataTypes = True
CopyAllPartitionFunctions = True
CopyAllPartitionSchemes = True
CopyAllSchemas = True
CopyAllSqlAssemblies = True
CopyAllUserDefinedAggregates = True
CopyAllUserDefinedTypes = True
CopyAllXMLSchemaCollections = True
CopyIndexes = True
CopyTriggers = True
CopyFullTextIndexes = True
CopyPrimaryKeys = True
CopyForeignKeys = True
All the security options are set to false.
When I execute this task, it fails (with the errorCode=-1073548784) at a point where it tries to create a table which is inside a schema, because the script for creating the table is executed before creating the schema. So, if have a table [Person].[Employee], the script "Create Table [Person].[Employee]" gives an error
The specified schema name "Person" either does not exist or you do not have permission to use it.
It cannot be a permission related issue as am using the sa account to connect to both the source and the destination.
Hence, I conclude that the SSIS task here tries to copy the table first without creating the schema and so the excution fails.
Is this a bug in SSIS? or am missing anything? and what can be the workaround this?
Hi,
When i'm trying to transfer SQL Server Objects from a SQL Server 2000 Database to another i got the following error: [Transfer SQL Server Objects Task] Error: Table "bank" does not exist at the source.
This just appens in some spcific situations:
- When i select the tables i want to transfer, using the option "CopyAllTables" it works fine;
- When i use specific instance as source. Using the Development machine as source it works fine, when i use the machine from Pre-Production (the one i whant to use) it doesn't .
Can anyone tell me why does this appens!?
Thanks,
VÃtor Ferreira
Hello all,
A little background... I have a 25GB database (called DevDB) that my co-workers use for SQL development. The data in this database isn't important and all I really need are the SQL objects. So instead of doing a database backup, which includes the data as well, I was planning on just copying the database objects to another database called DevObjects (on the same server) and backing it up instead. This is SQL 2005 SP2.
I've created an IS package and have 3 items...
1. Check for existance and drop DevObjects database. This is to eliminate the need for dropping the objects first. Successful.
2. Create a new database DevObjects. Successful.
3. Transfer SQL Server Objects Task. Fails.
My transfer objects task is setup like this:
The connections are to the same server, source db: DevDB, destination db: DevObjects.
DropObjectsFirst: False
IncludeExtendedProperties: True
CopyData: False
CopySchema: True
UseCollation: True
IncludeDependendObjects: True
CopyAllObjects: True
CopyDatabaseUsers: True
CopyDatabaseRoles: True
CopySqlServerLogins: False
CopyObjectLevelPermissions: True
CopyIndexes: True
CopyTriggers: True
CopyFullTextIndexes: True
CopyPrimaryKeys: True
CopyForeignKeys: True
GenerateScriptsInUnicode: True
I get an error trying to create a login that's not even in the database I am trying to copy the objects from. Since this is the same server, the login is already there.
Is there a way to not copy the server logins (so that CopyAllObjects works)?
Anyway, I got past that error by changing CopyAllObjects to False and setting all the subgroups to True. Now, I get an error creating one of the FK's, it seems that the PK it references isn't created first.
Is there a way to force creating the PK's before the FK's?
If I set CopyForeignKeys to False, then it completes successfully, but I need the FK's to be transferred as well. Any ideas?
Does anyone have a better way to accomplish what I'm trying to do?
Thanks in advance.
Jarret
I have the following problem:
All Sp's and UDF's in one base database have to be compiled into five identical databases located in the same SQL Server in order to keep all those five db's stored procedures synchronized.
I made a simple SSIS package which content one "Transfer SQL Server Objects Task" component.
Hardcoded connection/Source db/Destination db and setup properties as
"Drop object first" = True
"Copy All Stored procedures" = True
"Copy All UDF" = True
Then I executed the Package right from Visual Studio environment and after the substantial thinking finally it show me a green light.
But when I verified the result no Sp's in the destination database were re-compiled/copied from the source database.
Is it kind of bug or I did something wrong ?
Any advice will be appreciated.
What is the security requirement for running this task? I have no problem running this taks as a system administrator but all my users who are in the db_dtsltduser cannot run the same task successfully. They are DBOs in both databases involded in the task. Thank you in advance for your help.
View 2 Replies View Related https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127100
I don't believe that the fix to the above issue has made its way into an SP yet. Can anyone confirm/refute this, and/or state when the fix will become publicly available &/or give a workaround?
Thanks,
Tamim.
Hi everyone,
I'm currently having some trouble with the "Transfer SQL Server Objects Task".
All I wanna do is to copy a simple database from one server (SQL2000) to another one (SQL2005).
A small excerpt from my settings:
DropObjectsFirst: true
ExistingData: Replace
All table options are set to true
.
No matter what I do the package always appends the data from the source to the destination database.
Is this a bug or am I missing something ?
Thanks in advance,
Kevin
I am struggling to copy a 2000 DB to 2005 using transfer sql server objects task.
I can get it to work, but without the foreign keys, which I also need.
When also copying the foreign keys, I get the following message:"
[Transfer SQL Server Objects Task] Error: Execution failed with the following error:
"ERROR : errorCode=0 description=There are no primary or candidate keys in the referenced
table 'SVS' that match the referencing column list in the foreign key 'FK_WRM_SVS_WRM_SVS'.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Apparently, SQL tries to create a foreign key on the WRM table, while the primary key on SVS is not there yet.
Since it used to work in 2000, I am pretty sure I am doing something wrong. I can harldy believe this is by design.
(By the way, the CopyAllSchemas is set to True).
Can anyone please help?
Regards,
Pipo
Hello,
I am using the Transfer SQL Server Objects Task to copy Stored Procedures from one DB to another. I quite painfully discovered the problems with using the "DropObjectsFirst" flag in that if you set it to true and the object does not exist in the destination DB, SSIS throws a "Object does not exist" error. If you set it to false and the object exists at the destination DB, SSIS throws a "Object already exists" error. Sort of a catch 22 problem, so I decided to use a Data Flow task to build a Recordset of SP names that were common between the Source and Destination DB's and then feed that to a For Each task to drop the existing SP's from the destination DB before running the "Transfer SQL Server Objects" task.
So the problem is that I am getting 3 SP's copied over to the destination DB that do not exist on the source DB. If I delete all SP's on the destination DB and run the package it works fine the first time, when I run the package again, I get an "Object already exists" error at the destination DB for one of the SP's that don't exist on the source DB.
To explain the details of how I am building the list of SP names to be dropped, my Data Flow Task uses 2 DataReaders with a query to sys.objects to get a list of SP's from both DB's. This is then fed to the required Sort transforms and into a "Merge Join" Transform (Joint Type = Inner Join). The Merge Join Transform output is then passed to a RecordSet Destination variable.
When I query the source DB sys.objects, I can see all the SP's that were copied to the destination DB EXCEPT these 3 SP's. All the other SP's are successfully dropped on subsequent executions of the package except these 3. One of the 3 SP's (...Yardrequest...) does exist on the Source server (sort of) but when it gets copied over to the destination server, the Case of the name gets changed (only 1 letter in the middle of the name) to "...YardRequest...". I can't believe SQL is smart enough to capitalize each word in a SP name let alone what reasoning there could possibly be for doing such a thing.
Any ideas on where these 3 SP's are coming from?
My gut is telling me the DB got hosed somehow and therefore the Transfer SQL Objects Task is somehow seeing these addtional SP's that a simple query on the sys.objects view does not see. Not knowing how the "Transfer SQL Server Objects" task works internally (what queries it uses to identify what objects will be transferred from the source DB), I am not able to figure out where these SP's are located in the DB tables or views. Any pointers would be helpful. Thanks
I was wondering if there could be a race condition when using the Transfer SQL Server Objects Task to copy all tables from an online database.
I'm thinking of a scenario that while the copy task is executing, 2 different tables are updated in the online copy, with one new row each, and the new rows are linked by a key. Could it happen that when these values are inserted, one table has already been copied so the new value doesn't make it over, but the second table hasn't yet been copied so its new value does make into the destination (thereby breaking the relationship)? Or does this task take care of the details automatically?
Thanks!
Hi everyone,
I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio.
I've created an SSIS package. The following parameters are defined:
DropObjectsFirst true
IncludeExtendedProperties true
CopyData true
ExistingData Replace
CopySchema false
UseCollation false
IncludeDependentObjects true
CopyPrimaryKeys true
CopyForeignKeys true
The package fails with the following error:
Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus'
I know what that error means but I don't understand why I get it.
Isn't the package supposed to completely overwrite the destination database ?
It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS.
Hope anyone can help since this is almost driving me nuts ;-)
Thanks in advance,
Kevin
Using import task only can select and copy tables and views.
Since there are many objects at the same DB I don't need copy, is there any way let me select and copy some objects, such as functions, stored procedures?
I'm perplexed and in need of a sanity check ...
Instance of SQL2K5-SP1 on Server2003R2. Created two databases Test1 and Test2, create single 2-column table (tblTest)in Test1 with one row of data. Create SSIS package to transfer the table and its data to database Test2 using "Transfer Objects Task". Fails with error code 1073548784.
I have tested for security issues and don't beleive these to be the cause: I have monster privelidges and can successfully use SSIS packages to execute CREATE TABLE etc.
Have also tried most combinations of Task options (eg COPY ALL OBjects etc etc)
Have tried this on three different servers: same results.
This simple test is the result of distilling down problems I'm having with SSIS manipulating some 60G within some 300 tables, so I'd really like to use the power this task promises!
My head is getting a little sore from scratching - any advice gratefully received.
Thanks
Source Database: SQL Server 2000
Destination Database: SQL Server 2005
No matter what table, view, or stored proc I pick, it always says that it doesn't exist at the source. I know it exists because I am picking it from the list of tables, etc. that the GUI provides.
Any suggestions?
Jonathan Allen
I was trying to transfer a SQL Server 2000 database to SQL Server 2005 using SQL Server Objects Task. However, The following error message was encountered: "[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".€œ
View 12 Replies View RelatedSQL Server 2005 - June CTP
View 7 Replies View RelatedThe task has a fault if you try to copy a table with a schema like [test].[table1]
The error says
Error: 0xC002F363 at Flyt til DM, Transfer SQL Server Objects Task: Table "table1" does not exist at the source.
Even though i just selected it from the table list minutes before.
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.
I'm trying to copy data over using a Transfer SQL Server Objects Task however I get problems trying to copy over views. I get the similar error (pre sp1) when using import/export wizard and copying over views. Now with SP1 the views just don't transfer. I'm transferring from SQL 2005 to SQL 2005. The error I get is:
Error: 0xC002F363 at Transfer Data (NEW), Transfer SQL Server Objects Task: Table "vw_XXXX" does not exist at the source. Task failed: Transfer Data (NEW)
I know the view exists at the source because:
A) I can select it in the Views List Collection within the Transfer SQL Server Objects Task
B) I'm using the view on the source
Somehow the views are not transferring. Anyone have any ideas?
Hi
We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.
I set my Transfer Sql Transfer Objects Task to use the following settings:
CopyData = true
ObjectsTocopy = All Tables
As soon as it hits a table that has a foreign key, I get the following error:
"Truncate data failed for Table nnn"
I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.
First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?
Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?
Kind regards
Ben