SSIS custom task loses Expressions upon deployment

This is related to http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_24134460.html

When we deploy a package using a custom task I wrote to our TEST cluster, the package loses the Expressions for that task.

We deploy with the exact same script to our DEV clusters & it works just fine.

What could cause this?

The key parts of the batch file we use to deploy are below:
SET SSISServer=TestClusterSSISServer.MyDomain\ssists01
    SET SQLServer=TestCluster.MyDomain\datts01
    SET SSASServer=TestAnalysisServer.MyDomain\anlts01
    SET XMLAScript=SalesCubeScript-TEST.XMLA
 
 
        SET PackageName=CDR Load - Notifications
 
        ECHO.
        ECHO Checking for previous version of %PackageName%...
        dtutil /SQL "%FolderName%/%PackageName%" /SourceServer %SSISServer% /EXISTS
 
        IF ERRORLEVEL 1 GOTO DEPLOYFOLDER3PACKAGE3
            ECHO Deleting the previous version of %PackageName%...
            dtutil /SQL "%FolderName%/%PackageName%" /SourceServer %SSISServer% /DELETE
 
        :DEPLOYFOLDER3PACKAGE3
        ECHO.
        ECHO Deploying new version of %PackageName%...
        dtutil /FILE "%PackageName%.dtsx" /DestServer %SSISServer% /ENCRYPT SQL;"%FolderName%/%PackageName%";5 /Quiet

Open in new window

LVL 32
Daniel WilsonAsked:
Who is Participating?
 
Daniel WilsonAuthor Commented:
OK, it is the /ENCRYPT flag.

Change line 34 to the following, and it works.

dtutil /FILE "CDR Load - Notifications.dtsx" /DestServer MyServer\MySSISInstance /COPY SQL;"CDR Load/CDR Load - Notifications" /Quiet

Open in new window

0
 
Daniel WilsonAuthor Commented:
I've got a simplified deployment script that replicates the problem.

We do find that if we:
  1. Open Visual Studio on the server
  2. Open the package from a server that works
  3. Save As Copy up to the problem server
then it works.


 ::   SET SSISServer=MyServer\MySSISInstance
 
    ECHO.
    ECHO Beginning Deployment to TEST...
    ECHO.
 
 
    ::SET FolderName=CDR Load
 
    ECHO.
    ECHO Checking for folder CDR Load...
 
    dtutil /SourceServer MyServer\MySSISInstance /FEXISTS SQL;"CDR Load"
 
    IF %ERRORLEVEL% EQU 0 GOTO DEPLOYFOLDER3PACKAGES
        ECHO Creating CDR Load folder...
        dtutil /FC SQL;/;"CDR Load" /SourceServer MyServer\MySSISInstance
	
    :DEPLOYFOLDER3PACKAGES
    
        ::SET PackageName=CDR Load - Notifications
 
        ECHO.
        ECHO Checking for previous version of CDR Load - Notifications...
        dtutil /SQL "CDR Load/CDR Load - Notifications" /SourceServer MyServer\MySSISInstance /EXISTS
 
        IF ERRORLEVEL 1 GOTO DEPLOYFOLDER3PACKAGE3
            ECHO Deleting the previous version of CDR Load - Notifications...
            dtutil /SQL "CDR Load/CDR Load - Notifications" /SourceServer MyServer\MySSISInstance /DELETE
 
        :DEPLOYFOLDER3PACKAGE3
        ECHO.
        ECHO Deploying new version of CDR Load - Notifications...
        dtutil /FILE "CDR Load - Notifications.dtsx" /DestServer MyServer\MySSISInstance /ENCRYPT SQL;"CDR Load/CDR Load - Notifications";5 /Quiet
 
    ECHO.
    ECHO Deployment Complete!
    ECHO.
 
    PAUSE
 
    GOTO EXIT
 
    :ABORT
    ECHO.
    ECHO Deployment aborted.
    ECHO.
 
    PAUSE
 
    :EXIT
    ECHO.
    ECHO Deployment script ended.
    ECHO.
 
:: ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: END Clean Up
:: ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Open in new window

0
 
Daniel WilsonAuthor Commented:
Anyway ... anybody got a solution for a scripted deployment?  Our bosses are not going to like this workaround ...

Thanks!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
MIKESoftware Solutions ConsultantCommented:
I'm curious as to why you are using a "custom" deployment....?
0
 
Daniel WilsonAuthor Commented:
We need a script that we can deploy to multiple environments -- DEV, STAGE, TEST, PROD.

For the last 2 environments, we need to hand the script off to the infrastructure team ... so we need a script with minimal input required.  With our script they input one thing to indicate the environment to which it's being deployed and it does the rest.

is there a better or more reliable way to do this?

Thanks!
0
 
Daniel WilsonAuthor Commented:
In an hour we'll be testing without the /ENCRYPT flag ... just on  a hunch.  I'll let you know how that goes ... but would be very interested if you have a good idea why this would be happening -- or a better way to script the installation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.