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 SSIS PACKAGES FROM ONE PATH TO ANOTHER ONE

hi, does anyone know how can i move a group of ssis packages from the original path into another one in the same server? from ssis services? just like we do it with the windows explorer?

thanks for your help!!!!!!!!!!

May be it’s not the easiest way to do it, but it works for me:

1. Open MS Visual Studio, and create a new Integration Services project if you already don’t have one.

2. In the solution Explorer under SSIS packages - right click on the folder – Add existing packages (add original packages)

3. Right click on the project – Property.Specify the output path for your build.Click on Deployment utility and set “Create DepoloymentUtility” to true.Specify the output Path. Click OK.

4. Right click on the project – Build.The packages with a deployment utility should be under the directory specified in step 3.

5. Copy the entire deployment folder and paste it to the server you want to move the packages to.

6. Log in to the server and navigate to the directory made in step 5.Double click on the xxx.SSISDeploymentManifest file to initiate the deployment wizard.

a. Choose File System Deployment (Next)

b. Choose the new folder where you want your packages to be copied to (Next)

c. Finish the wizard, it should create packages in the new directory

Hope it helps…

|||

thanks it works fine, just for the record, there is another option i've found. using the 'dtutil' tool

there it is an example of it:

dtutil /MOVE SQL;destPackage /SQL srcPackage /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@.X /DESTUSER destUserName /DESTPASSWORD !38dsFH@.v||| Looks much cleaner..

Moving SSIS packages from one machine to the other

I have developed some packages in my desktop, which works fine. I tried to move the project from my desktop to laptop by just copying the whole project folder. When I open the package within the project, it used to validate each package and used to hang for a longtime (Due to connection string etc). As Jamie suggested DelayValidation = TRUE sorted that problem out, which means the package opens now without hanging.

I still have problems with the migrated package. It lost the layout of my package. Also it lost the link between components. In other words I have got components scattered around without any green / red / blue lines between them.

I am sure it cant be as painful this is to migrate a package from one machine to the other. I am sure I am doing something wrong!! Please help!

Thanks
SuthaYou are correct, this is not expected to happen. It is hard to say what's wrong though, I don't remember seeing this problem.

Which version of SSIS do you have on these machines? Is it the same version on both machines?

Do you use any custom task in the package? If yes, is the task installed on both machines?

Does it happen with many packages or a single one? Were some errors reported to the error list, or did you get any error message box?|||I got June CTP on both machine. Yes I am using Trash component, but it is installed on both machines.

I am concentrating on a one package at the moment, loading customer dimensions package.

There are no error messages.

Thanks
Sutha|||Could you please send me the package?

michen (at) microsoft-dot-com

Thanks,
Michael.|||Michael

I have sent it to you. Thanks for your help.

Thanks
Sutha

Moving SSIS Package

Hi,

I have to move my SSIS Project from Machine1 to Machine2 in a Development environment.when i open the Project in Machine2 .i have to restore all the connections in the connection manager. even after restoreing the connection sucessfuly .the package looks perfectly with out any error.but when i run the package.i am getting the following error

[Connection manager "Srv1.DBTest"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied ".

How to solve the problem.

Thanks

Jegan

I'd say the Oracle connection has lost the password, which may be caused by your Protectionlevel property for the package. I'd recomened using DoNotSaveSensitive, and then use a configuration to provide the connection details. This makes it easier if any connection detaisl chance as you jsut edit (common) configurations rather than having to chaneg the packages.

Setting the Protection Level of Packages
(http://msdn2.microsoft.com/en-us/library/d4b073c4-4238-41fc-a258-4e114216e185.aspx)

|||

Are you using Windows Authentication or Sql Server Authentication in your Oledb connection. Try using the user id /pass one which you were using in machine one ....second if you are using windows authentication log in the machine two with the same user id for which ur oledb connection manager is configured...

I think it should solve your problem.....:--)

|||

Darrne, its working fine . thanks for the help.

Vikram Thanks for the suggestion.

Moving sql 2000 DTS packages to sql 2005 standard edition

I have been tasked with upgrading around 150 SQL Server 2000 DTS packages to SSIS in SQL Server 2005 standard edition. I made a backup of the 2000 database upon which the DTS packages operate and restored it to the SQL 2005 server. So far, so good. I have the database in place. Now I need to get the DTS packages themselves into the SLQ 2005 server. I think I need to check my install and make sure that I have the SQL Server 2000 DTS services installed on the SQL 2005 server. I can do that.

However, I wonder what would be the most effective way to physically get the packages from the SQL 2000 server to the SQL 2005 server. Should I use structured storage files? If so, how do I go about opening them in SQL 2005 in order to save them to SQL server 2005?

I should mention that these packages make heavy use of ActiveX scripting so I am looking at rewriting them from scratch to be SSIS packages. I just need the packages on the SQL Server 2005 box so I can make sure I am creating exactly the same functionality in 2005 as existed in SQL server 2000. Each DTS 2000 individual package tends to be fairly simple and I think I can greatly improve the process by consolidating them.

Thanks in advance for any advice.

S. Wells

You'll need the DTS2000 editor by teh sounds of it. Downloadable from here: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

-Jamie

|||

Thanks Jamie,

It does appear that this will give me what I need. I had read another, earlier, post on the forum that indicated the legacy components did not have to be separately installed now that 2005 is in production release if one had chosen to install integration services. I did not realize that the designer was not installed as part of the legacy DTS components install. I looked all over the BI interface and could not see where I could open a DTS package and then save it to SQL 2005.

Again, thanks.

S. Wells

|||

Just a quick note to say that there is a new version (an update to the previous link):

Instead of:

Feature Pack for Microsoft SQL Server 2005 - November 2005
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Download from:

Feature Pack for Microsoft SQL Server 2005 - April 2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

6th item down:

Microsoft SQL Server 2000 DTS Designer Components

The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Audience(s): Customer, Developer
X86 Package (SQLServer2005_DTS.msi) - 5088 KB

Also look at

SQL Server 2005 Books Online

SQL Server 2005 Integration Services Backward Compatibility
Updated: 17 July 2006
http://msdn2.microsoft.com/en-us/library/ms143706.aspx

General Compatibility between DTS and SSIS
Because Integration Services is an entirely new product, and not a version upgrade, you will not encounter software conflicts between DTS and SSIS.

You can install both DTS and SSIS on the same server.
You can run both DTS and SSIS packages on the same server when both products are installed. You can run DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS runtime that is installed with Integration Services.
You can save DTS packages in the MSDB database of a SQL Server 2000 or SQL Server 2005 instance. You can save SSIS packages only on a SQL Server 2005 instance.
You can edit DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS Designer that is available for download. You cannot edit DTS packages in Business Intelligence Development Studio. You can edit SSIS packages only in BI Development Studio.

moving package from dev to live

I moved one of the ssis packages from dev to prod.
Did the same for the configuration file which contains the connectionstring.
Now I would like to run the package in live but the error is:

Description: The configuration file name "S:\Imports\Trades\Environment.dtsConfig" is not valid. Check the configuration file name.

The file does indeed exist and the connectionstring has the correct string.

Any thoughts please?

How are you executing the package? If via 'SQL Agent' then you need to specify the CONFIG file in the command line, i.e. : - DTEXEC /FILE "<<MyFullPackagePath>>" /CONFIGFILE "<<MyFullConfigPath" /CHECKPOINTING OFF