How to save SSIS packages on SQLserver2005 MSDB

Question

How to save SSIS packages on SQLserver2005 MSDB

Farhad-386799

Say Hey Kid

Points: 681
More actions
January 18, 2007 at 1:56 pm

#171858

Hello,
How can I save SSIS package in SQLserver2005? In 2000 you are able to view pacakges under data transformation and they are backed up when MSDB is being backed up.
Any idea thanks much

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MegaDBA Hall of Fame Points: 3518 More actions · Answer 1

Are you trying to create a new SSIS package or save a 2000 DTS package in SQL 2005?

The tool in SQL 2005 to manipulate SSIS packages is SQL Server Business Intelligence Development Studio. Within this gui you will be able to create and modify SSIS packages. You can also migrate SQL 2000 DTS packages here.

Christine~

Aurora

Farhad-386799 Say Hey Kid Points: 681 More actions · Answer 2

Thanks, I am more interested in how to view packages from microsoft SQL server Management Studio. In 2000 DTS packages can be viewed directly from Enterprise manager, but in 2005 I am not sure how to link it.

My main objective is that developers can create package in a central area and we should be able to view all production packages that could be scheduled to run on regular basis.

Thanks Farhad

MegaDBA Hall of Fame Points: 3518 More actions · Answer 3

SQL Server has changed the way the DTS packages are displayed and developed. In 2005 to develop SSIS packages you use a tool that installs with SQL Server Integration Services. In order to develop these pakages which now have a .dtsx extension you MUST use the SQL Server Business Intelligence Studio. This is the central area you describe that you would like to view and develop packages.

Aurora

PStair SSC Journeyman Points: 78 More actions · Answer 4

Aurora,

I still don't understand how to have a centralized view and development scenario for our developers. When I try to save an SSIS package using the Business Intelligence Studio, it tries to save them as an external file, and it presents me with the drives and folders that are available to my login session. Unlike the DTS packages in SQL Server 2000, it doesn't appear to give me the option to save it in SQL Server itself. Are we supposed to save these DTSX project files to a centralized folder somewhere, or is there a method in the BI Studio to save these SSIS packages in SQL Server that I just haven't found yet?

- Patrick

SQLBill SSC Guru Points: 51440 More actions · Answer 5

In the 2005 BOL (Books OnLine), filter to SSIS, and look up Deployment Utility. The package must be part of a solution. In the properties of the solution, you can turn DeploymentUtility to TRUE. Then select Build. Then find the .SSISDeploymentInstallationManifest. Double click on that and the deployment utility starts. Select the option for storing it on SQL Server, then the option to allow security on the storage level.

-SQLBill

Farhad-386799 Say Hey Kid Points: 681 More actions · Answer 6

The best thing to do is to load SSIS packages on the server, so this way they are located on the server and developer can export them in case they want to view them.