?
Solved

SSIS custom task loses Expressions upon deployment

Posted on 2009-02-13
8
Medium Priority
?
440 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:Daniel Wilson
  • 5
6 Comments
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23654229
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23654239
Anyway ... anybody got a solution for a scripted deployment?  Our bosses are not going to like this workaround ...

Thanks!
0
 
LVL 17

Assisted Solution

by:MIKE
MIKE earned 200 total points
ID: 23662784
I'm curious as to why you are using a "custom" deployment....?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23662882
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23662894
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 0 total points
ID: 23663811
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question