Automate MSBI deployments Using PowerShell

  • Comments posted to this topic are about the item Automate MSBI deployments Using PowerShell

  • This link is not working for me ; "too many redirects".

  • Hi I get the following message in chrome when trying to access the article.

    This page isn’t working

    http://www.sqlservercentral.com redirected you too many times.

    ERR_TOO_MANY_REDIRECTS

    Tried clearing cookies but it didnt open.

    Thanks!

  • The link is working now.
    Please check http://www.sqlservercentral.com/articles/MSBI/161004/

    Thank you!

  • Great article. I was wondering if there is a neat way to force the run order that the sql scripts for the database. Often one script is dependent on another. Will this simply run them in alphanumeric order?

  • Hello,

    Yes, the scripts will run in alphanumeric order.

    As .sql files may be dependent, we can prefix the file names with numbers e.g 1parentscript.sql, 2childscript.sql etc. So parent files will be deployed first followed by child.
    Hope this article helps you.

    Regards,
    Kunal

  • Hi All, I'm quitte new to Powershell.
    I have version 5 installed and only wanted to try the SSIS deployment part, but I'm getting an error:
    Error: Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is loaded..Exception.Message
    #########################################################################
    End of deployment with failure(s). Execution end time 2017.10.19 02:31:00 

    Did is mis something on the powershell configuration? I have SQL SERVER 2016 installed with SSIS/SSAS services.

  • Hello,

    Thank you for trying this framework for deployment.

    In order to help you on this issue, I would like to know if you were able to deploy SSIS build (.ispac) file manually on the server ? You will only be to able to deploy SSIS build via PowerShell if you were able to do it manually.

    Note: I believe you have enabled integration services catalog on SQL server.

    Below is some similar issue lthread which might help you with this issue.

    https://stackoverflow.com/questions/41308202/deploying-ssis-package-failed-to-load-assembly-microsoft-sqlserver-management

    Thank you!

    Kunal

  • This process seems a bit manual. Is there a reason the process, does not go and look for the ispac file itself, or grab sql files from a location.
    Also is there a way to stop it from timing out as if I'm running scripts, all the more reasons I don;t want to wait around to manually run them.

  • Hello,

    The process has been designed to consider agile development requirements in the project where developers check-in their respective code changes (.sql files, .rdl files etc) to a particular build. The build once freezes for release, goes through various environment. This is the reason we need to copy all the relevant files to a particular location. 

    There is a way to stop timing out of .sql files by adding a line of code in SQL Server related PowerShell scripts. 
        {
          $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLSTMT, $SQLConnection)
         $SQLCommand.CommandTimeout=1000     
               $value = $SQLCommand.ExecuteScalar()
        } 

    Here, timeout value(1000) is in seconds. 
    Hope this will help you in your deployments.

    Thank you!
    Regards,
    Kunal

  • I do not see the link to download the code. please include the url

  • Hello,

     

    I am not sure why the downloadable file is not appearing. It used to be there before the recent update in sqlservercentral.

    I will try to figure that out. Meanwhile, you could refer to my blog on blogger for the downloadable.

    https://msbitechgig.blogspot.com/2019/02/automate-msbi-deployment-using.html

    Hope this helps.

    Regards,

    Kunal

     

  • Interesting article.  Thanks for your work!  What I'd also like to see is auto build/deployment from source control using Continuous Integration Servers like Jenkins or TeamCity and other auto-build tools, possibly including an artifact archive step using something like Nexus or Artifactory.

    Gerald Britton, Pluralsight courses

  • Thank you for your interesting suggestions.

    Lets us connect over LinkedIn and take this idea ahead

     

     

     

     

     

     

  • This reply has been reported for inappropriate content.

    I'm very grateful to the author, this really changed my thought. I'm looking forward to acknowledge this

    website as one of the best for new information.

    Thor Jacket

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply