asked on

SSIS custom task loses Expressions upon deployment

This is related to https://www.experts-exchange.com/questions/24134460/SSIS-custom-task-Property-is-not-getting-value.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

Daniel Wilson

ASKER

I've got a simplified deployment script that replicates the problem.

We do find that if we:

Open Visual Studio on the server
Open the package from a server that works
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

Daniel Wilson

ASKER

Anyway ... anybody got a solution for a scripted deployment? Our bosses are not going to like this workaround ...

Thanks!

SOLUTION

Marcus Aurelius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Daniel Wilson

ASKER

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!

Daniel Wilson

ASKER

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.

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial