Copying SSIS Packages

Is there any way to copy SSIS packages from one mirrored server to another? I'm considering using robocopy, but is there another solution? I'd then like to use this solution with the Transfer jobs task.Robocopy works for me in this scenario. Are you using a file-based or SQL Server deployment for the packages?|||I'm storing all packages in the msdb (package store) and we have our jobs pointing to the msdb. With SQL2000, we used the "transfer package" method described in www.sqldts.com, where we simply queried the table from our primary and copied those records to our warm-standby. With that solution on sqldts.com, is it possible to use the "sysdtspackages90" table to achieve the same results? I've also read that "dtutil" can be used to "copy" packages. In my case, I'm not sure on which method would be best though. Or, would it be better to store the packages in the file system?|||

DTUTIL is a good option if you are going with database storage. If you are using file storage, it isn't as valuable (unless you are also resetting GUIDs or passwords when you move them).

My personal preference is file system deployment. Others like database deployment. It really comes down to your preferences and specific requirements (though most requirements can be met be either).

Copying Dimensions in BIDS

Is it possible to copy a dimension from one solution/project to another. I have a generic time dimension and a few other common dimensions that I would rather just copy from one project to another rather than setting it up entirely from one project to another.

I tried copying the XML file from one project to another, but that did not work because they had different DSV's, and thus different design-time unique names.

any suggestions?

-ClaytonUsing SSMS, you should be able to script out the creation of the dimension and then execute it in the other database. If you are using different DSV (or DS) names, then you will have to modify the script before you execute it on the other system but hopefully that will be easy. At least it is just editing a single file.

_-_-_ Dave

Copying a report to another report in same solution

How can I copy a report and all its supporing queries and make a duplicate so that report1 and report2 are duplicates.

Report 2 will be little different than report1 but the basic queries and parameters will all the same. I can then edit the report layout to what the new report will look like. I do not want to have to cut/paste one object at a time.

Is there a better way?

You can do this directly from within Visual Studio.

Right click on the report you want to copy (in Solution Explorer) and select 'Copy' (or you can just highlight your report and hit Ctrl+C). Then press Ctrl+V. This will create a new duplicate report with 'Copy of ' prefixed on the report name.

Hope this helps.

Jarret

|||Copy does not work as there is no "Paste" function. The Ctl C/V works just fine. Thank you.

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
=============
VB .NET Developer
http://www.rocketscience.uk.com
Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
--
=============
VB .NET Developer
http://www.rocketscience.uk.comMatt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy SQL Server 2005 Express database to SQL Server 2000

I think the easiest solution is to go to Properties|Options for the database
and change the compatibility level to (80) Sql Server 2000 then backup the
database and restore it to the new 2000 server.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Angus" wrote:
> Hello
> I have a copy of a SQL Server 2005 Express database - the mdf and .ldf
> files. If I copy to my machine and in the Enterprise manager try to
> attach
> the files I get error 602 - and apparently you cannot do this as the
> database structure has changed so much.
> Bearing in mind SQL Server 2005 is customersw so don't really want to
> install too much software on their machine. so if I can do this all my
> end
> that would be preferable. Can I somehow convert the 2005 database to run
> on
> my SQL Server 2000? What would I need to install on my machine to achieve
> this? The SQL Server 2005 Express does not seem to provide much in the
> way
> of data export etc.
> Angus
>
Dave Patrick (DSPatrick@.nospam.gmail.com) writes:
> I think the easiest solution is to go to Properties|Options for the
> database and change the compatibility level to (80) Sql Server 2000 then
> backup the database and restore it to the new 2000 server.
No, this does not work. The compatitibility level only affects how T-SQL
code is parsed and behaves. It has nothing to do with the on-disk structure
for the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Oops, Ok thanks for clarification Erland.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Erland Sommarskog" wrote:
> No, this does not work. The compatitibility level only affects how T-SQL
> code is parsed and behaves. It has nothing to do with the on-disk
> structure
> for the database.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Copy SQL server 2000 database structure

Hello,

Is there some solution to copy database using VB.NET 2003 code with all constraints ,specifications and relationships ?

Thnx in adv

you may script the database store it in a file. then use the "shell" command to run sqlcmd using the script you generated

or you may wan't to progaram using the sql SMO.