Opening package stored in MSDB

I created a package using the Import\Export wizard and saved it to the local Sql server. Under Management Studio I can see the package, can run in, import it, export it BUT how am I supposed to edit (modify the thing) it?

TIA,

barkingdog

You can make simple edits like changing the property, enabling / disabling task etc by exporting the SSIS package as dtsx files in file system and opening it in SQL Server Management Studio.

To perform more advanced functionalities -like adding new tasks / flows - you need to open and edit the dtsx package in Visual Studio / SQL Server Business Intelligence Development Studio.

Thanks,
Loonysan

|||

Loonysan,

What you described strikes me as totally counter-intuitive. Under sql 2000 after I create and save a simple package, I can double-click it in Enteprse manager (the package opens), and proceed to work on it. End of story.

If I undestood what you said, I have to export my sql 2005 package (say, stored in sql servr) to a dtsx file and then import that into BIDS. This seems overly complicated to me.

Thanks,

Barkingdog.

|||

In SQL 2000 one edited and managed DTS packages in the same environment. This was perhaps ok when packages were simple utilites for the DBA. But in SQL 2005, SSIS Packages are much richer objects and have a full developer environment to support them, as well as a better management environment.

So in 2005 we have BIDS for developing (editing) and Management Studio for administration of deployed packages.

Just like a code project, one typically develops in a specialised environment and then deploys to the server. The developers code (package) is stored where the developer wants it - perhaps in version control. The deployed version lives on the server.

Modifying the version on the server is not good practice. One should modify the version kept in the development environment and deploy the new version to the server.

Some have discovered that it is possible to open a package in the management studio, but as pointed out, this is a most limited experience as it does not have the full range of development features. It may be useful for a quick fix to a property, but even then I would not recommend it.

Is this more complex than in 2000? Perhaps, but practices will should be better for it.

Donald

|||How do you find the SSIS package to save as a file once it is in MSDB? When I go to SSIS, the package itself doesn't show. I'm using Instances, and it will only open for the default install|||

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

|||

AndyHzzzz wrote:

Megan,

You need to go on to the server and edit the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

Change <ServerName>.</ServerName> to <ServerName>(your server)\(instance)</ServerName>

Then restart SSIS.

I agree with you that this is counter-intuitive. I am so disappointed with all of SQL Server 2005. Everything has become harder to use, and it's plagued with bugs and problems. I don't know what MS was thinking.

Andy,
Did you read Donald's post? *That* is what Microsoft was thinking and I agree with it. SSIS is more of an enterprise development tool, not the toy that DTS was and hence it needs to follow a more rigid methodology.|||

Phil,

I did read Donald's post, and I understand what he is saying. However, that doesn't excuse the fact that I can create a package in the Import/Export wizard, configure it, run it, re-run it, re-configure it, etc., all from within that wizard, but if I ever close that wizard, I have to go to a completely different environment to work with that package again. That, to me, is bad interface design.

Furthermore, consider the actual steps in all of this. Today I needed to import 4 tables from Access to SQL Server. These were the steps involved (excluding cursing and searching Books Online):

Started Import/Export wizard

Attempted import of 4 Access tables using default options

Got errors regarding data conversion, clicked 'Back' to the mappings dialog, changed datatypes (Repeat)

Successfully imported the data.

Closed the Wizard.

Later, realized that I wanted to change some datatypes of the target fields.

Looked for my package in Management Studio. Got an error when selecting MSDB store

Modified MsDtsSrvr.ini.xml file on server. Re-started Integration Services (good thing I have admin privileges)

Found my package, exported to file, opened in Visual Studio.

Attempted to modify the SQL statement "Preparation SQL Task", where the target tables are created.

Got an error telling me that "CREATE TABLE" statements are not allowed, and cannot be parsed (the wizard created this package, remember. Also, this SQL statement was full of embedded carriage returns, so I could only edit the first CREATE TABLE statement anyway).

Copied the CREATE TABLE statements into a text file, modified the task to read the SQL from that file.

Changed the datatypes in the text file.

Ran the package. Success.

What did I gain from this new methodology? Nothing. I was not trying to develop an enterprise application, I just wanted to import 4 tables! This story is typical of my experiences with 2005. Simple tasks have become adventures through seemingly untested tools.

Furthermore, even if I did need to develop the sort of app that SSIS is geared toward, I don't trust it. I have run into too many weird problems with this whole release.

Andy