Storage system for SSIS packages(SQL Server , File System, SSIS package store)


  • Hello SSIS experts,


    I just wanted to confirm the difference between SQL Server storage and SSIS Package store for packages. As seen from the BIDS, the SQL Server list packages from msdb(point to note is the folder MSDB is not visible here) and SSIS Package store(MSDB folder is visible) and the authentication is defaulted to Windows (disabled)  from both the file system and msdb?  Just wanted to know if there are any other differences to be noted or request to let me know if there are any corrections to be madein my views?

    Happy to help! Thanks. Regards and good Wishes, Deepak.
    Friday, May 20, 2011 10:12 AM


  • Dear Deepak;

    I explained them one by one:


    File System:

    I'm sure you know exactly what the FileSystem option is, but to complete this section: you can save the package on a physical location on hard drive or any shared folder with this option, and you should provide a full qualified path to stored package in the FileSystem option.


    SQL Server:

    with this option, SSIS packages will be stored in the msdb database, in the sysssispackages table. you can separate them in any folder you want. these folders are virtual folders, which listed in the sysssispackagefolders table. so with this option ssis package will not store as physical file. it will store in the msdb database.


    SSIS Package Store:

    this option is not actually a real option for deployment. why? because this is a redirection step only. what I mean is that if you choose this option in the BIDS when you try to save a copy of package ... , you will see a tree with a parent "SSIS packages" and two child : "MSDB" and "File System". if you choose the "MSDB" the package will save under msdb database same as the sql server option ( previous option which described ).if you choose the "File System", the ssis package will store as a physical file with .dtsx extension but in a folder in this address: <Program files folder>\Microsoft SQL Server\100\DTS\Packages . this is like file system option but you can not store package at any location , you can store it under a predefined physical location and of course you can create subfolders there, but you can not cross the parent to save package any other place than predefined.


    Friday, May 20, 2011 1:41 PM