Problem with "Transfer SQL Server Objects Task"

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

I've had the same problems, and for what it's worth, here's what I found:

setting copySchema to true fixed a few problems

CopyForeignKeys.....it doesn't work, so don't do it. SSIS seems to try and create foreign keys before it has created all the primary keys, so it can sometimes fall over

check jamie thomson's article for possible workarounds of the foreign key problem:
http://blogs.conchango.com/jamiethomson/archive/2006/02/17/SSIS_3A00_-How-to-load-related-tables.aspx

do you have service pack 2 installed?

michal
|||Hi Michael,

I've also experimented with 'copySchema'. No luck so far.
Service pack 2 is installed on both servers.
I've read the article you're referring to and I just can't believe that the "Transfer... Task" isn't able to perform such a simple thing.
|||Oops, sorry for the chaos.
The previous post was also made by me. I currently have two Passport identities.
I'll try to not use the other one anymore...

*edit*
This is starting to become really weird. I've manually created a database (no tables, sps etc.) on the destination server. When I tell the "transfer... task" to copy the production database from the source server to this new database it gives me the following error:

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

Of course the table doesn't exist. The package is supposed to create it. A lack of permissions can't be the problem as well since I'm sysadmin.

I'm really stuck here. SQL 2005 has been out for quite a while now. I just can't believe that this is a bug.
|||Hi Kevin

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

That means that you have set 'dropObjectsFirst' to true. It's trying to drop a table that doesn't exist. Your best bet is to either drop the tables before hand and set 'dropObjectsFirst' to false, or make sure the objects exist on the target DB.

although you have SP2 on both servers, are you creating the package on one of those servers? Or do you have SP2 on the machine that you are creating the package?

Everything I've read about this suggests using backup/copy/restore, which isn't brilliant.

The only thing I can really suggest is stripping the package right down to simply copying a few tables, then rerunning it over and over adding more and more options until it falls over. Trial and error I know, but at least you will know the SSIS limitations

michal

problem using "com.microsoft.jdbc.sqlserver.SQLServerDriver"

Hi.
the problem is probably just that your servlet code is not thread-safe.
Multiple threads are probably trying to use the same jdbc objects.
Define all your jdbc objects as method-level variables and you should
be OK...
Joe Weinstein at BEA
Simon Lee wrote:
> hi
> I am using "com.microsoft.jdbc.sqlserver.SQLServerDriver" driver to connect
> the SQL Server 2000.
> Everything is ok if there is one request to the SQL server.
> When there are more than one requests to the SQL server, exception was
> thrown
> I want to ask if that driver has some problems to handle multi-request.
> Thanks
> The expcetions are:
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has
> been closed.
> at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown
> Source)
> at com.microsoft.jdbc.base.BaseResultSet.validateClos edState(Unknown
> Source)
> at
> com.microsoft.jdbc.base.BaseResultSet.commonFetchI nitialize(Unknown Source)
> at com.microsoft.jdbc.base.BaseResultSet.next(Unknown Source)
> at GetData.GetData.getColumn(GetData.java:192)
> at GetData.GetData.insert_fileInstance_table(GetData. java:1156)
> at RadicaPocketServer2.handleRequest(RadicaPocketServ er2.java:1056)
> at RadicaPocketServer2.doPost(RadicaPocketServer2.jav a:55)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:760)
> at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
> at
> org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(Application
> FilterChain.java:247)
> at
> org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterCh
> ain.java:193)
> at
> org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.ja
> va:256)
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> at
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
> at
> org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.ja
> va:191)
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> at
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
> at
> org.apache.catalina.core.StandardContext.invoke(St andardContext.java:2415)
> at
> org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:180
> )
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> at
> org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatcherValve.
> java:171)
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> at
> org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:172
> )
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> at
> org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:509)
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:641)
> at
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
> at
> org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java
> :174)
> at
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
> eNext(StandardPipeline.java:643)
> at
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
> at
> org.apache.catalina.core.ContainerBase.invoke(Cont ainerBase.java:995)
> at
> org.apache.ajp.tomcat4.Ajp13Processor.process(Ajp1 3Processor.java:466)
> at
> org.apache.ajp.tomcat4.Ajp13Processor.run(Ajp13Pro cessor.java:585)
> at java.lang.Thread.run(Thread.java:534)
>
Simon Lee wrote:

> Hello Joe
> Thanks for you suggestion, but I still have questions about it.
> My servlet is run in tomcat 5.0 and I know that tomcat help us to handle
> multiple threading problem
> Could you explain your idea to me more. Thank you
> Also, I have written a class called GetData.java which represent to connect
> and execute SQL statement.
> GetData is used by my servlet program to handle database connections
> Do you mean that I need to handle mutli-threading in GetData.java's
> functions
> Thank you very much
A servlet is a single class that can be run simultaneously by many
threads. Therefore you must make sure that no two threads are
using the same jdbc objects. If you have a single GetData class,
it must create, use, and close all the jdbc objects it is going to use
within each method it has. It shouldn't have permanent jdbc objects
that get re-used, unless you make your own thread-safe pooling code
that makes sure that no two threads use the same objects.
Otherwise you must make your servlet so that only one thread
can run it at a time.
Joe Weinstein at BEA
> Simon Lee
> "Joe Weinstein" <joeNOSPAM@.bea.com> ? news:407FFE48.30900@.bea.com ?...
>
> connect
>
> has
>
> com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown
>
> com.microsoft.jdbc.base.BaseResultSet.validateClos edState(Unknown
>
> Source)
>
> RadicaPocketServer2.handleRequest(RadicaPocketServ er2.java:1056)
>
> org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(Application
>
> org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterCh
>
> org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.ja
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
>
> org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.ja
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
>
> org.apache.catalina.core.StandardContext.invoke(St andardContext.java:2415)
>
> org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:180
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.valves.ErrorDispatcherValve.in voke(ErrorDispatcherValve.
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:172
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.valves.AccessLogValve.invoke(A ccessLogValve.java:509)
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
>
> org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java
>
> org.apache.catalina.core.StandardPipeline$Standard PipelineValveContext.invok
>
> org.apache.catalina.core.StandardPipeline.invoke(S tandardPipeline.java:480)
>
>