Using SSIS to refresh a training database from a production database

Question

Hey All,

I have a particular system on out network where we need to maintain a training installation. The system uses SQL Server 2000 as its database engine and I need to set up a system for refreshing the data in the training database with the data from the production database on a regular basis.

I want to use SSIS as we have SQL 2005 servers I can run the process from. I have a fair bit of SQL experience, but not much with SSIS. I have been trying to do this with the "Transfer Database Task" but haven't been having much luck, as it always throws an error.

If we ignore the use of configuration items etc and pretend all the database names and so forth are hard-coded, I have the following: A Single SSIS "Transfer Database Task" with the following properties:

• Destination Overwrite: True
• Action: Copy
• Method: DatabaseOnline

The error I receive is:

Error: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_addrole @rolename = N'XXXXX' " failed with the following error: "The role 'XXXXX' 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.

I'm sure there is something obvious going on here, but surely if the task is set to overwrite the pre-existance of the role shouldn't matter? Does anyone know what I need to do to get this working?

Answer 1

Are you sure that the user that the task is using to access the database has the correct permissions to overwrite roles? This won't be the same as a simple datawriter permission? Are you using SQL Server Authentication or Windows Authentication, are you running from withing SSIS in Visual Studio or as a Job in SQLServer?

Answer 2

Good questions Matt - I should have included that info.

I am running it from Visual Studio at this stage as I am still testing.

The database connection is set to use windows authentication and I am a database and network administrator. So permissions shouldn't be an issue. I also checked the database server and it actually creates the new testing database, but doesn't populate it with anything (roles, users, tables, views or stored procedures).

Answer 3

Apparently this should be fix in SQLServer 2005 SP2 see here. Looks like you need to make sure to patch the client machine too if you are running the SSIS package from within Visual Studio.

Answer 4

I know this is old, but did you ever get this solved? I installed sp2 and I'm still having the same issue