Jerome Smith


Hello all,

Does anybody know where SSIS Data Connections are stored Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know

Thanks in advance,

Jerome Smith





Re: Data Connections: Where the hell are they stored??!!

jaegd


The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.






Re: Data Connections: Where the hell are they stored??!!

Jerome Smith

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database) I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers

Cheers,

Jerome







Re: Data Connections: Where the hell are they stored??!!

jaegd

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens Runs as before. Which database is hit The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).





Re: Data Connections: Where the hell are they stored??!!

Jerome Smith

Thank you very much. That was very useful.

Best regards,

Jerome Smith