Moving SSIS project

I am trying to move an entire SSIS project from one machine to another (with a different directory structure) in a Development environment. In addition to the packages themselves I have Data Sources and Data Source Views defined. I also have connection managers for Excel and SQL Server that use those data sources, and connection managers for flat files for my error logs. Finally, I have saved my package configurations in an XML file.

I've followed the steps in the following post to move my packages:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817897&SiteID=1

What else exactly do I need to do, though, to move my complete project. My objective is to start up BIDS on the new server, File -> Open -> Project/Solution and point it to the new <project>.sln file and have it work. I know I need to edit my config XML file, but other than that, how do I properly move/recreate the data sources, data source views, and solution files in the new location. I've tried several things, but with no luck.

Thanks!

N Bauer wrote:

I am trying to move an entire SSIS project from one machine to another (with a different directory structure) in a Development environment. In addition to the packages themselves I have Data Sources and Data Source Views defined. I also have connection managers for Excel and SQL Server that use those data sources, and connection managers for flat files for my error logs. Finally, I have saved my package configurations in an XML file.

I've followed the steps in the following post to move my packages:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817897&SiteID=1

What else exactly do I need to do, though, to move my complete project. My objective is to start up BIDS on the new server, File -> Open -> Project/Solution and point it to the new <project>.sln file and have it work. I know I need to edit my config XML file, but other than that, how do I properly move/recreate the data sources, data source views, and solution files in the new location. I've tried several things, but with no luck.

Thanks!

You just have to copy the sln file and the sub-folder containing the .ds, .dsv, .dtsx and other files to the new machine. And yes, you should use package configuration to set up connections, paths and other property values that can change from environment to environment.

Did you get any error when opening the project in BIDS? what exactly is you problem?

|||

Sorry for the delay, but I was out of town and then got sick. Anyway, I have gotten the hang of moving packages/projects and using configuration files. For the benefit of others here are the two things that caused me the most grief:

1. Connection managers and configuration files - just as soon as you save a configuration file it takes precedence over any settings within the package itself you might make within BIDS. For database connections, the password IS NOT SAVED in the configuration file. Therefore, immediately within BIDS it appears that your connection managers are hosed as you will get error messages to that effect. Thus, I recommend the following steps for using XML configuration files:

Save the configuration file

Open the package you want to create a configuration file for

Choose SSIS - > Package Configurations

Check "Enable package configurations", be sure to choose the connection managers from the pick list, and save the xml file to the location/name of your choice.

Finish

Immediately, close and save the package.

sql

Moving SSIS project

I am trying to move an entire SSIS project from one machine to another (with a different directory structure) in a Development environment. In addition to the packages themselves I have Data Sources and Data Source Views defined. I also have connection managers for Excel and SQL Server that use those data sources, and connection managers for flat files for my error logs. Finally, I have saved my package configurations in an XML file.

I've followed the steps in the following post to move my packages:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817897&SiteID=1

What else exactly do I need to do, though, to move my complete project. My objective is to start up BIDS on the new server, File -> Open -> Project/Solution and point it to the new <project>.sln file and have it work. I know I need to edit my config XML file, but other than that, how do I properly move/recreate the data sources, data source views, and solution files in the new location. I've tried several things, but with no luck.

Thanks!

N Bauer wrote:

I am trying to move an entire SSIS project from one machine to another (with a different directory structure) in a Development environment. In addition to the packages themselves I have Data Sources and Data Source Views defined. I also have connection managers for Excel and SQL Server that use those data sources, and connection managers for flat files for my error logs. Finally, I have saved my package configurations in an XML file.

I've followed the steps in the following post to move my packages:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=817897&SiteID=1

What else exactly do I need to do, though, to move my complete project. My objective is to start up BIDS on the new server, File -> Open -> Project/Solution and point it to the new <project>.sln file and have it work. I know I need to edit my config XML file, but other than that, how do I properly move/recreate the data sources, data source views, and solution files in the new location. I've tried several things, but with no luck.

Thanks!

You just have to copy the sln file and the sub-folder containing the .ds, .dsv, .dtsx and other files to the new machine. And yes, you should use package configuration to set up connections, paths and other property values that can change from environment to environment.

Did you get any error when opening the project in BIDS? what exactly is you problem?

|||

Sorry for the delay, but I was out of town and then got sick. Anyway, I have gotten the hang of moving packages/projects and using configuration files. For the benefit of others here are the two things that caused me the most grief:

1. Connection managers and configuration files - just as soon as you save a configuration file it takes precedence over any settings within the package itself you might make within BIDS. For database connections, the password IS NOT SAVED in the configuration file. Therefore, immediately within BIDS it appears that your connection managers are hosed as you will get error messages to that effect. Thus, I recommend the following steps for using XML configuration files:

Save the configuration file

Open the package you want to create a configuration file for

Choose SSIS - > Package Configurations

Check "Enable package configurations", be sure to choose the connection managers from the pick list, and save the xml file to the location/name of your choice.

Finish

Immediately, close and save the package.

Moving SQL Server 2000 to another server

I will be moving the SQL Server 2000 databases from Server A to Server B.
Both the servers will be having the same directory structure. Here is what I
will be doing:
1. Backup and restore master from Server A to Server B
2. Backup and restore msdb and model from Server A to Server B
3. Detach all the user databases from Server A, copy the .mdf and .ldf files
to Server B and attach the databases on Server B.
4. Shut down Server A.
5. Rename Server B to Server A.
Any suggestions will be highly appreciated. Thanks.
If the folder layouts are identical, then you could just shut down Server A,
copy the whole Data folder(s) over and then start up Server B. Just be sure
to run sp_dropserver and sp_addserver when you are done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
I will be moving the SQL Server 2000 databases from Server A to Server B.
Both the servers will be having the same directory structure. Here is what I
will be doing:
1. Backup and restore master from Server A to Server B
2. Backup and restore msdb and model from Server A to Server B
3. Detach all the user databases from Server A, copy the .mdf and .ldf files
to Server B and attach the databases on Server B.
4. Shut down Server A.
5. Rename Server B to Server A.
Any suggestions will be highly appreciated. Thanks.
|||Thanks a lot, Tom and Tibor. This was really helpful.
"Tom Moreau" wrote:

> Oh yeah, you're renaming it back to the original. Missed that one. In that
> case, you're OK without those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:026FECD7-11D8-40FA-8161-64786D6D410E@.microsoft.com...
> Thanks for the information. If I am going to rename the server B to server A
> after shutting down server A, what will I achieve by running the
> sp_dropserver and sp_addserver?
> "Tom Moreau" wrote:
> A,
> sure
> I
> files
>
>

Moving SQL Server 2000 to another server

I will be moving the SQL Server 2000 databases from Server A to Server B.
Both the servers will be having the same directory structure. Here is what I
will be doing:
1. Backup and restore master from Server A to Server B
2. Backup and restore msdb and model from Server A to Server B
3. Detach all the user databases from Server A, copy the .mdf and .ldf files
to Server B and attach the databases on Server B.
4. Shut down Server A.
5. Rename Server B to Server A.
Any suggestions will be highly appreciated. Thanks.If the folder layouts are identical, then you could just shut down Server A,
copy the whole Data folder(s) over and then start up Server B. Just be sure
to run sp_dropserver and sp_addserver when you are done.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
I will be moving the SQL Server 2000 databases from Server A to Server B.
Both the servers will be having the same directory structure. Here is what I
will be doing:
1. Backup and restore master from Server A to Server B
2. Backup and restore msdb and model from Server A to Server B
3. Detach all the user databases from Server A, copy the .mdf and .ldf files
to Server B and attach the databases on Server B.
4. Shut down Server A.
5. Rename Server B to Server A.
Any suggestions will be highly appreciated. Thanks.|||And here's some additional (good) reading:
http://vyaskn.tripod.com/moving_sql_server.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OexzxW2jIHA.484@.TK2MSFTNGP06.phx.gbl...
> If the folder layouts are identical, then you could just shut down Server A,
> copy the whole Data folder(s) over and then start up Server B. Just be sure
> to run sp_dropserver and sp_addserver when you are done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
> I will be moving the SQL Server 2000 databases from Server A to Server B.
> Both the servers will be having the same directory structure. Here is what I
> will be doing:
> 1. Backup and restore master from Server A to Server B
> 2. Backup and restore msdb and model from Server A to Server B
> 3. Detach all the user databases from Server A, copy the .mdf and .ldf files
> to Server B and attach the databases on Server B.
> 4. Shut down Server A.
> 5. Rename Server B to Server A.
> Any suggestions will be highly appreciated. Thanks.
>|||Thanks for the information. If I am going to rename the server B to server A
after shutting down server A, what will I achieve by running the
sp_dropserver and sp_addserver?
"Tom Moreau" wrote:
> If the folder layouts are identical, then you could just shut down Server A,
> copy the whole Data folder(s) over and then start up Server B. Just be sure
> to run sp_dropserver and sp_addserver when you are done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
> I will be moving the SQL Server 2000 databases from Server A to Server B.
> Both the servers will be having the same directory structure. Here is what I
> will be doing:
> 1. Backup and restore master from Server A to Server B
> 2. Backup and restore msdb and model from Server A to Server B
> 3. Detach all the user databases from Server A, copy the .mdf and .ldf files
> to Server B and attach the databases on Server B.
> 4. Shut down Server A.
> 5. Rename Server B to Server A.
> Any suggestions will be highly appreciated. Thanks.
>
>|||Oh yeah, you're renaming it back to the original. Missed that one. In that
case, you're OK without those.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ronnie" <ronnie@.discussions.microsoft.com> wrote in message
news:026FECD7-11D8-40FA-8161-64786D6D410E@.microsoft.com...
Thanks for the information. If I am going to rename the server B to server A
after shutting down server A, what will I achieve by running the
sp_dropserver and sp_addserver?
"Tom Moreau" wrote:
> If the folder layouts are identical, then you could just shut down Server
A,
> copy the whole Data folder(s) over and then start up Server B. Just be
sure
> to run sp_dropserver and sp_addserver when you are done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
> I will be moving the SQL Server 2000 databases from Server A to Server B.
> Both the servers will be having the same directory structure. Here is what
I
> will be doing:
> 1. Backup and restore master from Server A to Server B
> 2. Backup and restore msdb and model from Server A to Server B
> 3. Detach all the user databases from Server A, copy the .mdf and .ldf
files
> to Server B and attach the databases on Server B.
> 4. Shut down Server A.
> 5. Rename Server B to Server A.
> Any suggestions will be highly appreciated. Thanks.
>
>|||Thanks a lot, Tom and Tibor. This was really helpful.
"Tom Moreau" wrote:
> Oh yeah, you're renaming it back to the original. Missed that one. In that
> case, you're OK without those.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> news:026FECD7-11D8-40FA-8161-64786D6D410E@.microsoft.com...
> Thanks for the information. If I am going to rename the server B to server A
> after shutting down server A, what will I achieve by running the
> sp_dropserver and sp_addserver?
> "Tom Moreau" wrote:
> > If the folder layouts are identical, then you could just shut down Server
> A,
> > copy the whole Data folder(s) over and then start up Server B. Just be
> sure
> > to run sp_dropserver and sp_addserver when you are done.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "ronnie" <ronnie@.discussions.microsoft.com> wrote in message
> > news:233B8469-52BB-484B-9ADC-BDC5A81D7081@.microsoft.com...
> > I will be moving the SQL Server 2000 databases from Server A to Server B.
> > Both the servers will be having the same directory structure. Here is what
> I
> > will be doing:
> > 1. Backup and restore master from Server A to Server B
> > 2. Backup and restore msdb and model from Server A to Server B
> > 3. Detach all the user databases from Server A, copy the .mdf and .ldf
> files
> > to Server B and attach the databases on Server B.
> > 4. Shut down Server A.
> > 5. Rename Server B to Server A.
> >
> > Any suggestions will be highly appreciated. Thanks.
> >
> >
> >
>
>

Moving Log Files

I have inherited a SQL Server environment that was haphazardly installed and managed. I am in the process of trying to apply some form of structure to this environment. Currently all data and log files reside in the default data directory. I am moving the
log files to a separate directory on a different drive. I know I can use sp_detach_db and sp_attach_db to move the log files, but how can I move the log files for master, msdb, model, and tempdb?
Any help would be greatly appreciated.
http://www.databasejournal.com/featu...le.php/3379901
|||Erik,
Thanks for the reply. This was exactly what I was looking for.
"erik perez" wrote:

> http://www.databasejournal.com/featu...le.php/3379901
>

Moving Log Files

I have inherited a SQL Server environment that was haphazardly installed and
managed. I am in the process of trying to apply some form of structure to t
his environment. Currently all data and log files reside in the default data
directory. I am moving the
log files to a separate directory on a different drive. I know I can use sp_
detach_db and sp_attach_db to move the log files, but how can I move the log
files for master, msdb, model, and tempdb?
Any help would be greatly appreciated.http://www.databasejournal.com/feat...cle.php/3379901|||Erik,
Thanks for the reply. This was exactly what I was looking for.
"erik perez" wrote:

> http://www.databasejournal.com/feat...cle.php/3379901
>