Automate MSBI deployments Using PowerShell

  • Kunal Rathi

    SSC Veteran

    Points: 268

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

  • stian.bakke

    SSC Enthusiast

    Points: 142

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

  • ahmadbaara

    SSC Enthusiast

    Points: 120

    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!

  • Kunal Rathi

    SSC Veteran

    Points: 268

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

    Thank you!

  • seanoregan

    SSC-Addicted

    Points: 421

    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?

  • Kunal Rathi

    SSC Veteran

    Points: 268

    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

  • hans_peeters_

    Grasshopper

    Points: 21

    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.

  • Kunal Rathi

    SSC Veteran

    Points: 268

    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

  • mailbox.jon

    SSC Enthusiast

    Points: 103

    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.

  • Kunal Rathi

    SSC Veteran

    Points: 268

    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

  • kilambi

    Newbie

    Points: 1

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

  • Kunal Rathi

    SSC Veteran

    Points: 268

    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

     

  • g.britton

    SSChampion

    Points: 13686

    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, MCSE-DP, MVPToronto PASS Chapter[/url]

Viewing 13 posts - 1 through 13 (of 13 total)

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