locked
How do I create a folder for SSIS package in MSDB with T-SQL? RRS feed

 > 

  • General discussion

  • Discussion
    Sign in to vote
    3
    Sign in to vote
    [This is just for sharing information for those common asked questions collected from forums. If you have any better way or feedback, please directly reply in this thread so that community users can benefit from your answers.]


    Question:

    When I logon to Integration Services from SQL Server Management Studio, I can create a package folder to store the package under the MSDB folder. But can I programmatically create a folder with T-SQL?

     

    Answer:

    Yes, you can directly insert a record into the user table dbo.sysssispackagefolders in your msdb database with the T-SQL statement as follows:

    INSERT INTO dbo. sysssispackagefolders (
folderid
,parentfolderid
,foldername)
VALUES (NEWID(), '<Parent folder GUID>', '<Folder name>')

     

    For example, if you want to create a folder named “Packages” under the MSDB folder, you can run the following T-SQL:

    INSERT INTO dbo. sysssispackagefolders (
folderid
,parentfolderid
,foldername)
VALUES (NEWID(), '00000000-0000-0000-0000-000000000000','Packages')
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Saturday, June 19, 2010 3:51 AM

All replies

  • Discussion
    Sign in to vote
    0
    Sign in to vote
    Charles - you should be blogging these tips.  Search engines and other blogs will find them more readily...
    Todd McDermid's Blog Talk to me now on
    Saturday, June 19, 2010 3:48 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote
    Its better you create some blog, and use it in your footer while answering the questions...
    Saturday, June 19, 2010 7:42 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Hi Todd and bal123,

    Thanks very much for your feedbacks. Some colleagues and I collected about 20 SSIS and 20 SSAS forum FAQs and need to post them in the SSIS and SSAS forums in the following days.

    I had thought that blog is a good way, however based on our investigation and statistics from other forums like .NET development, windows server etc, posting the FAQs in forums has better better search possibility than a blog. For example, if you search a technical question vial Bing or Google, forum posts are often ranked very top. So we follow the best practice from other forums and post these FAQs in forums.

    To align the FAQs publication manner with other forums, I am afraid that currently these 40 coming FAQs will be published in SSIS and SSAS forums. However we really appreciate your feedbacks. We will further discuss on the FAQ publication and figure out a better way for doing this next time, maybe creating a blog or KB or something else.

    Really appreciate your understanding on this. Thank you guys.

     

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Sunday, June 20, 2010 5:48 AM
  • Discussion
    Sign in to vote
    1
    Sign in to vote

    My two cents:

    It may or may not be the case that forum posts get ranked higher - in my experience, blog posts get ranked equally.  However, I tend to click on blog posts in preference to those - because forum posts tend to be rambling and not always applicable.  From a blog post title, I can easily see if the post will apply, and blog posts tend to be very straightforward, to the point, and complete.

    Pages get ranked on relevance and links to them (I think) - which may mean that forum posts get linked to comparably to blog posts... which may the case.

    If/when yo do compile those posts into a FAQ, I hope you don't copy the entirety of the posts into the FAQ.  You should just place a link to the relevant post in there.

    However, I think you can get the best of both: Publish your articles on a blog - perhaps the SSIS Team blog, under a "How To" tag, and link to those posts in the FAQ.  You'll benefit from the rankings that the SSIS Team blog already has - plus the fact that it's already on a ton of peoples' reader lists.

    Todd McDermid's Blog Talk to me now on
    Sunday, June 20, 2010 5:16 PM
  • Discussion
    Sign in to vote
    0
    Sign in to vote

    Thanks Todd. We will change the way of FAQ publication next time. For this time, let us just bear them in forums.

    Thank you!

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Sunday, June 20, 2010 5:41 PM
  • Discussion
    Sign in to vote
    4
    Sign in to vote

    Wouldn't it be cleaner/safer to use the appropriate system stored proc for that?

    The example would come down to:

     

    exec msdb.dbo.sp_ssis_addfolder
    @parentfolderid = '00000000-0000-0000-0000-000000000000'
    ,@name = 'Packages';

    Additional info: the third parameter, @folderid, is optional and NEWID() will be used when not provided.

     

    MCITP SQL Server 2008 (DEV); MCTS SQL Server 2008 (BI, DEV & DBA)
    • Edited by Valentino Vranken Wednesday, January 25, 2012 12:36 PM added additional info
    Wednesday, January 25, 2012 12:34 PM