ETL and Data Integration tools - Modern ETL software for everyone

I've created dozens of import scripts for a database in SQL Server. I have another database (actually, the production database) with identical tables. Is there a way to suddenly change all of the database references in the imports from one database to another? Can I just modify the exported repository and then reimport it?

I can't find much information about the repositories in the documentation other than that the data is stored there. Could I export the MS Access repository and then import it into a SQL Server database? What about moving the repository to a parallel machine? Can I just copy the relevent .mdb files to the other machine? Is there some documentation somewhere that tells more about the repository and options for using it?

Is there a way to suddenly change all of the database references in the imports from one database to another?

I think all you need to do is to change connection settings.

>Could I export the MS Access repository and then import it into a SQL Server database?

Yes, it is possible.

Tools->Export repository and Tools>Import Repository

Have you actually created Sql server repository?

>Can I just copy the relevent .mdb files to the other machine?

This should work as well

OK, production database Dom, development Dom2, for testing this I created Dom3. All three databases are in one instance of MS SQL Server. Tree looks like:

Objects
...
-MS SQL Server
--sql2 (my connection)
---dom az item mst

"dom az item mst" is an import which loads into
[Dom2].[dbo].dc_item_master_az table.

I then edited the properties for "sql2" so that now the database selected is Dom3 instead of Dom2.

When I then run the import "dom az item mst" it still loads into Dom2.

It appears that changing the connection settings in "sql2" is insufficient to get it to load into the alternate database. Is my only option editing each of dozens of import scripts to modify which database and table they update? Is there no way I can edit the exported repository or something in order to change them all at once?

Just to make it more clear for you

To create and move data beetween repositories

1. Create Repository using script provided within default repository
2 Connect to the repository you want to export repository data from
3 click tools -> export repository
that will create a zip file
4 Connect to the repository you have created on step 1
5 click tools -> import repository

to change repository connection

1 click file->Options
2 choose appropriate Repository connection name
3 click reconnnect

to create a new repository connection

1 click file->Options
2 click plus
3 fill in all appropriate fields
4 click ok
4 click reconnnect

OK, so I'm playing with this some more. Now I'm trying to edit the import "dom az item mst". The "Data Target Options" doesn't allow me to pick a table from any database other than the original Dom2, even though my connection, "sql2", has been edited so that Dom3 is the default database. I tried going into the template and editing the text referencing Dom2 to now reference Dom3. This occurred on two lines, the ones labelled "Database" and "TargetTable".

No go. The import still loads into the table in Dom2.

Surely this can be done. Otherwise I'll have to redefine all of these imports on the production database in order to get them to update the production database. This introduces all sorts of possibilities for errors. That's why I'm developing them on the development db to make sure they are all working correctly. Then I want to switch the tested imports to update the tables of the same name in the production database. Is there something else I should try?

OK, seeing your second reply above I think maybe we are not on the same page. Re reading my own first message in this thread I'm not sure I was clear about what I was asking. In fact, I think I wound up asking two different questions in the single thread.

I believe I understand about moving repositories between databases from the earlier reply. Now I'm talking about updating imports and packages which I've developed using one MS SQL Server database so that they will all now point to a different MS SQL Server database. If I have to export the repository, edit the exported repository, and then import it to have the changes made, then I think I see how to do that, but are the exported repositories readily editable? I've looked at one and there seem to be some non-ASCII (or at least some non-alpha-numeric characters that I don't recognize) in the files.

I hope this clarifies my question. Sorry for the confusion.