"File System" vs MSDB packages?

Hello.
I was hoping to learn more about the differences between MSDB and "File System" Integration Services (IS) packages.

First, why do I have the option to choose?

Second, is there a way in T-SQL to query any information on File System packages? With the MSDB packages, I can query msdb.dbo.sysdtspackages90 .

Thanks for any info.

Kirk has a good post on the pros and cons of saving to file or SQL Server here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

There is no way, using T-SQL, to get information about file system packages. SQL Server does not know about such packages. The only slight caveat to this is that the file can be set to log to SQL Server - in which case there will be some info from the package in SQL Server - but SQL Server doesn't "know" about the package as such.

-Jamie|||Kirk has a good discussion

http://sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx

What type of info would you like from the package on disk?

Allan

"JFoushee@.discussions.microsoft.com"

wrote in message

news:c8e13f2e-2b59-4cb1-8b6a-5bb9e65b18b4@.discussions.microsoft.com:

> Hello.

> I was hoping to learn more about the differences between MSDB and "File

> System" Integration Services (IS) packages.

>

> First, why do I have the option to choose?

>

> Second, is there a way in T-SQL to query any information on File System

> packages? With the MSDB packages, I can query msdb.dbo.sysdtspackages90

> .

>

> Thanks for any info.|||What type of info would you like from the package on disk?

I was hoping to get directories and/or names of IS packages in the File System.

I think the "root" File System folder is stored as a Registry key.
However, the only place I found it was in HKLM\Software\Microsoft\Windows\CurrentVersion\Installer\UserData\... , which suggests a per-user strategy. This doesn't make sense.|||There is no root package. The packages exist wherever you put them.

-Jamie|||I thoroughly agree there is no "root" package.
However, there is a root folder in Windows Explorer that corresponds to the File System folder in SSMS.
(In my case, C:\Program Files\Microsoft SQL Server\90\DTS\Packages)
This folder name is stored as a value in a Registry key.

When I add a folder or package to either
- C:\Program Files\Microsoft SQL Server\90\DTS\Packages in Windows Explorer
- the "Stored Packages"\"File System" folder in SSMS,
it will appear in the other.

I accept Jamie's answer that I cannot use TSQL to view the "File System" folder contents.

Wasn't sure if there some xp_cmdshell trickery that involves this Registry key, so that it would work for every SQL 2005 configuration. Probably not.