Schedule a package to copy table schema/data and any procs/views from one database to another

Question

Post reply

Schedule a package to copy table schema/data and any procs/views from one database to another

Matt Crilly

Valued Member

Points: 58
More actions
June 15, 2008 at 9:23 pm

#183443

Hello,
I am hoping someone will be able to help me with this problem.
I have two databases hosted on 2 separate shared hosting servers. The problem I am having is trying to synchronise a backup version of one database - (table schema and data) from one server to a secondary server.
The backup / copying process should happen every day - where the table schema and table data is copied from the source database and then replicated on the secondary database.
I have 2 different usernames and passwords to access each database and I've tried a few approaches to create a SSIS package to perform the task, but I just can't get it to execute.
Can anyone offer any suggestions - I have exhausted my google credits on this one and can't seem to find any useful answers.
Cheers,
Matt.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

VG-619426 Hall of Fame Points: 3914 More actions · Answer 1

Hi Matt,

Did u try using transfer database objects task in SSIS ???

-V

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 2

Only requirement if you are transferring objects is that you have SP2 installed (which is absolutely key in any event to successfully running SSIS).

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127100

With the transfer objects task, you will be playing around with SMO. Works with different users on different machines.

Good luck

~PD

Matt Crilly Valued Member Points: 58 More actions · Answer 3

Thanks for your responses, I have checked and sp2 is installed.

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I am trying to use the Transfer SQL Server Objects task, but when I try to run it I get the following error:

Error: The Validate method on the task failed, and returned error code 0x80131501 (Failed to connect to server XXX.). The Validate method must succeed and indicate the result using an "out" parameter.

When I look at and test both of my connections, they succeed without error, but when I run the transfer in debug mode it fails.

Any ideas?

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

pduplessis-723389

SSChampion

Points: 10599

June 18, 2008 at 12:42 am

#830223

Did you specify SMO connections?

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 5

I received the same error when I changed the security of the package to "Dont save sensitive".

Fixed this by changing it to save with password.

One question though? What kind of impact does this have on package configurations (I want to store my SMO connections in configurations, complete with username and password)

Matt Crilly Valued Member Points: 58 More actions · Answer 6

Yes,

the ConnectionManagerType = SMOServer for both connections

Matt Crilly Valued Member Points: 58 More actions · Answer 7

I have made some slight progress, I am now able to connect the SSIS package to both servers (i have created 2 connection config files which store the username/passwords for each connection)

Now, when I run the package without debugging, I get this error:

Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null.."

In the log file it has this error code: -1073548507

Can anyone offer some suggestions to fix this?

Thanks