Automate MSBI deployments Using PowerShell

Question

Post reply

Automate MSBI deployments Using PowerShell

Kunal Rathi

SSC-Addicted

Points: 455
More actions
September 14, 2017 at 12:00 am

#328390

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

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

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

stian.bakke SSC Enthusiast Points: 143 More actions · Answer 1

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

ahmadbaara SSC Enthusiast Points: 124 More actions · Answer 2

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.

Try clearing your cookies.

ERR_TOO_MANY_REDIRECTS

Tried clearing cookies but it didnt open.

Thanks!

Kunal Rathi SSC-Addicted Points: 455 More actions · Answer 3

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

Thank you!

seanoregan SSC-Addicted Points: 421 More actions · Answer 4

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-Addicted Points: 455 More actions · Answer 5

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 More actions · Answer 6

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-Addicted Points: 455 More actions · Answer 7

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: 133 More actions · Answer 8

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-Addicted Points: 455 More actions · Answer 9

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 More actions · Answer 10

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

Kunal Rathi SSC-Addicted Points: 455 More actions · Answer 11

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: 13863 More actions · Answer 12

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

Kunal Rathi SSC-Addicted Points: 455 More actions · Answer 13

Thank you for your interesting suggestions.

Lets us connect over LinkedIn and take this idea ahead

hassaankhan Newbie Points: 1 More actions · Answer 14

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