none
SSIS Package Deployment to msdb

    Question

  • I have designed a few SSIS packages in the BI Development studio and deployed them to the msdb database of my SQL server using the generated Deployment Utility.

     

    They deployed and executed just fine, but, I would like to better organize them into folders within the msdb storage area.

     

    Is there a way to tell the project or the generated Deployment Utility to deploy the packages to a specific folder within the SSIS Packages / msdb storage area on the SQL server?

     

    Thank you for any help!

     

    -Tim

    Monday, February 27, 2006 8:06 PM

Answers

  • Hi Tim,

    Create a folder in MSDB say TIM_Packages.
    Create a same folder i.e. TIM_Packages on you file system from where you will installing SSIS packages. Keep all the SSIS Package under this folder.

    Create a batch file having the code given below.
    @ECHO OFF

    :PRE1
    SET /P SSISSERVER=[Enter SSIS Server Name with Instance Name ] :

    IF %SSISSERVER%"dummy" == "dummy" (
        echo No server name passed.;
        GOTO :PRE1
    )


    FOR %%f in (.\*.dtsx) Do (
        ECHO Deploying %%f as %%~nf
        dtutil /Q /Fi %%f /C "SQL;"%%~nf /DestS %SSISSERVER%
    )


    for /d %%X in (.\*) do (echo %%X
        FOR %%f in (%%X\*.dtsx) Do (
            ECHO Deploying %%f as %%~nX\%%~nf
            dtutil /Q /Fi %%f /C "SQL;"%%~nX\%%~nf /DestS %SSISSERVER%
    ) )

    ECHO "Deployment complete"

    PAUSE

    execute the batch file. It will ask for the SSIS server name. Pass the server name and it will directly install all the SSIS Package to the folder.


    • Proposed as answer by Kunal Joshi Wednesday, February 24, 2010 8:10 AM
    • Marked as answer by Bob BojanicOwner Monday, March 01, 2010 6:33 PM
    Wednesday, February 24, 2010 7:46 AM

All replies

  • I have designed a few SSIS packages in the BI Development studio and deployed them to the msdb database of my SQL server using the generated Deployment Utility.

     

    They deployed and executed just fine, but, I would like to better organize them into folders within the msdb storage area.

     

    Is there a way to tell the project or the generated Deployment Utility to deploy the packages to a specific folder within the SSIS Packages / msdb storage area on the SQL server?

     


    HI tim
    Create a folder in msdb say TIMPACKAGES
    the use dtutil to move the installed packages (via normal manifest deployment utility u use to now) to the newly created folder
    as dtutil /SourceServer YOUR_SourceServer_NAME /SQL TIM_PACKAGE_1 /MOVE DTS;MSDB/TIMPACKAGES/TIM_PACKAGE_1
    .
    .
    .
     and so on .... for all the installed packages
    hope that helps ... kunal
    • Proposed as answer by Kunal Joshi Wednesday, February 24, 2010 7:44 AM
    Wednesday, February 24, 2010 7:28 AM
  • Hi Tim,

    Create a folder in MSDB say TIM_Packages.
    Create a same folder i.e. TIM_Packages on you file system from where you will installing SSIS packages. Keep all the SSIS Package under this folder.

    Create a batch file having the code given below.
    @ECHO OFF

    :PRE1
    SET /P SSISSERVER=[Enter SSIS Server Name with Instance Name ] :

    IF %SSISSERVER%"dummy" == "dummy" (
        echo No server name passed.;
        GOTO :PRE1
    )


    FOR %%f in (.\*.dtsx) Do (
        ECHO Deploying %%f as %%~nf
        dtutil /Q /Fi %%f /C "SQL;"%%~nf /DestS %SSISSERVER%
    )


    for /d %%X in (.\*) do (echo %%X
        FOR %%f in (%%X\*.dtsx) Do (
            ECHO Deploying %%f as %%~nX\%%~nf
            dtutil /Q /Fi %%f /C "SQL;"%%~nX\%%~nf /DestS %SSISSERVER%
    ) )

    ECHO "Deployment complete"

    PAUSE

    execute the batch file. It will ask for the SSIS server name. Pass the server name and it will directly install all the SSIS Package to the folder.


    • Proposed as answer by Kunal Joshi Wednesday, February 24, 2010 8:10 AM
    • Marked as answer by Bob BojanicOwner Monday, March 01, 2010 6:33 PM
    Wednesday, February 24, 2010 7:46 AM
  • hi, i have installed visual studio 2010,sql server 2008 and 2012.i have created ssis package in vs2010 and when i am trying to deploy this package in sql server it is giving error like...

    

    the version of the specifies sql server instance does not support the selected operation. specify a different sql server instance(microsoft.sql server.management.integration service)

    operation not supported on version 10.50

    Please help me....

    
    
    
    
    

    Monday, April 23, 2012 9:36 AM