Installing/Updating MSSQL server database with dacpac

[yusuf]

Hello,
We use a data tier application to deploy our database to MSSQL server. [A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC. https://docs.microsoft.com/en-us/sql/re ... plications]. DACPAC is deployed using SqlPackage.exe utility. Sqlpackage can be run from command line or batch file.

We would like to use MSI to package our dacpac and the sqlpackage utlity. To achieve that I have been looking though your documentation and forms and could not find enough information. There is some documentation I found here http://www.advancedinstaller.com/forums ... hp?t=28845

I would like to be able to do the following
1. Collect from the user parameters that I will be passing to Sqlpackage utility. The parameters include Server Name, Database Name, User Name and password.
2. All the files in the MSI are not meant to be installed in the target machine, rather to be executed to the time of install. Which means no files to deploy, no entry in the registry and no entry in the "Add/Remove Program" list. It is acceptable to copy the files to the target machine for the purpose of executing them.
3. Being able to create custom UI to be able to collect user information to pass to "custom action".

Thanks,
Yusuf

[Daniel]

Hello Yusuf and welcome to our forums,

A possible implementation would be:

For the first question :

-Go to "Dialogs" page and create a new dialog, for example after "WelcomeDlg"
-On this new dialog create an edit box for each user parameter you want to pass to Sqlpackage.
-Change the Property name for each edit box. For example for the edit box containing the user name change it to USERNAME_PROP. Use all upper-case letters for this properties.

For the second question:

-If you don't need the files on the target machine, you could use "Temporary Files". Temporary Files" are copied on the target machine at the beginning of the installation and are deleted at the end of it, ensuring you have access to them throughout the entire install process.
-Go to "Files and Folders" page and click on [Temporary Files] toolbar button. Select your files

For the third question:
-The information will be collected through the dialog you created.
-You need to create a new "Launch Attached File" custom action for SqlPackage.exe and enter the command line you need. You can use property formatted references in the command line field. For example : "/TargetUser [USERNAME_PROP]" .In this way the value entered by the user in the username edit box will be passed to the command line.

All the best,
Daniel

[yusuf]

Hi Daniel,
Thanks for the detailed response. I was able to follow your suggestion and able to create MSI Package. but now I am running into an issue with the Custom Action.

2017-05-04 16_06_05-Vision 8 Database - Vision 8 Database.aip (English US) - Advanced Installer 13.8.png (15.86 KiB) Viewed 8721 times

If I run the custom action from the command line ( the package is deployed into the temp folder)
C:\Users\yabdulkadir.CORP\AppData\Local\Temp\V8DB>SqlPackage.exe /action:publish /sourcefile:Vision8.dacpac /TargetServername:DV-LT-59 /TargetDatabaseName:SqlPackageDeployment
it works fine. But running the MSI Package I get an error

Action 13:50:49: sqlpackage.exe.
MSI (s) (A8:50) [13:50:49:526]: Executing op: CustomActionSchedule(Action=sqlpackage.exe,ActionType=1042,Source=C:\Users\YABDUL~1.COR\AppData\Local\Temp\V8DB\sqlpackage.exe,Target=/action:Publish /SourceFile:TempFolder\V8DB\Vision8.dacpac" /TargetServerName:DV-LT-59 /TargetDatabaseName:SqlPackageDeployment,)
CustomAction sqlpackage.exe returned actual error code 1 (note this may not be 100% accurate if translation happened inside sandbox)
MSI (s) (A8:50) [13:50:49:823]: Note: 1: 1722 2: sqlpackage.exe 3: C:\Users\YABDUL~1.COR\AppData\Local\Temp\V8DB\sqlpackage.exe 4: /action:Publish /SourceFile:TempFolder\V8DB\Vision8.dacpac /TargetServerName:DV-LT-59 /TargetDatabaseName:SqlPackageDeployment
Error 1722. There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Action sqlpackage.exe, location: C:\Users\YABDUL~1.COR\AppData\Local\Temp\V8DB\sqlpackage.exe, command: /action:Publish /SourceFile:TempFolder\V8DB\Vision8.dacpac" /TargetServerName:DV-LT-59 /TargetDatabaseName:SqlPackageDeployment
MSI (s) (A8:50) [16:01:46:340]: Product: Vision 8 Database -- Error 1722. There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Action sqlpackage.exe, location: C:\Users\YABDUL~1.COR\AppData\Local\Temp\V8DB\sqlpackage.exe, command: /action:Publish /SourceFile:TempFolder\V8DB\Vision8.dacpac" /TargetServerName:DV-LT-59 /TargetDatabaseName:SqlPackageDeployment

How to I identify and fix the error.

Thanks
Yusuf

[Daniel]

Hello,

I noticed that you use a quote mark in your command line that hasn't a corresponding starting quote mark. You should use the formatted reference for your V8DB folder instead of "TempFolder\V8DB\". To achieve this, right click in the "Command Line" field and select "Folder" option, then select V8DC folder. Please notice that the formatted reference when resolved will also include also the last "\" character of V8DC folder path, so you don't need to enter it again before Vision8.dpac. Have a look on the screenshots attached.

Also, this error could be encountered if your exe needs elevation at launch time. You should set the custom action to "Deferred" option and select "Run under the LocalSystem accound with full privileges(no impersonalization)" option too. You should also uncheck "Fail installation if custom action returns an error" option as some exe files have different return codes than "0" which are interpreted as errors by Windows Installer.

screenshot1.jpg (51.91 KiB) Viewed 8711 times

screenshot.jpg (34.82 KiB) Viewed 8711 times

Hope this works!

Daniel

[yusuf]

Hi Daniel,
Thank you for your detailed instruction. I am able to resolve the issue with running Sqlpackage using 'Launch Attached File" custom action. I have further question related to the MSI working on.
1. I have created a dialog to collect user information. In the dialog I want to Auto populate Server Name and Database. So,
a. Does Advanced Installer function that will populate the Server names in the network then for given server all the database?
b. If that is not available, I can write custom code (C#) that will do the work for me. In that case can I call custom code ( may be custom action ) to call my code?

2017-05-05 14_25_24-Vision 8 Database - Vision 8 Database.aip (English US) - Advanced Installer 13.8.png (19.63 KiB) Viewed 8693 times

2. Cascading Custom Action: I noticed that I can execute custom action to call a function from my DLL. But the Execution option is only to fail the installation? Based on the return of my call (fail) can I execute different custom action, for example Show error message or run powershell script? How can I Cascade multiple custom action and fork based on the result of the previous call?

Thanks again for your continued support.
Yusuf

[Sorin]

Hello Yusuf,

1. Yes, Advanced Installer has this option. You need to go to "Dialogs" page and add "SQLServerConnectionDlg" predefined dialog.Some default database connection, database browse and server browse will be generated. You can configure their settings from "SQL Databases" page.

2. This can be done in the following way:

-Create a custom action
-In this custom action use a Property that conditions the trigger for the second custom action. In this article you could find out how to set installer properties using custom actions: How to set an installer property using custom actions
-Execute the second custom action based on the Propery contained by the first custom action (just use the above property in the "Condition" field of the second action)

Hope this helps!

Best regards,
Sorin

[yusuf]

Hi Daniel,
Couple follow-up questions:
1. SQLServerConnectionDlg is great and exactly what I needed. How to make sure Server, Database and Authentication ( either checked Trusted connection or User Name and Password ) are all entered before the user clicks Next. In other words I want to prevent the user from proceeding if any entry is not valid or missing?
2. In my Custom Action I am passing Server and Database in the command line from SQLServerConnectionDlg. For example (/action:Publish /SourceFile:[V8DB_Dir]Vision8.dacpac /TargetServerName:[SERVER_PROP] /TargetDatabaseName:[DATABASE_PROP]). This works if I am using Trusted Connection. In the case of the user enters User Name and Password in SQLServerConnectionDlg, I want to pass it to my custom action command line. How do I figure out if Trusted Connection is being used, if not how do I modify the command line to include User Name and Password.

Thanks
Yusuf

[Sorin]

Hello,

1. For achieving this you need to take the following steps:
- go to "Custom Actions" page and create a new "Display message box custom action". This message box will prompt the user to enter the Username or Password if the values are missing. Enter the message to be displayed in the "Message" field and the Title of the message box in the "Title" field.
- go to "Dialogs" page, to SQLServerConnectionDlg and click on the "Next" Button, and select Published Events
- create a new Publish Event for - Execute custom action for your Display Message Box Custom Action. Place this event at the top of the published events list. In the condition field enter the following condition:

Code: Select all

((USERNAME_PROP = "" OR PASSWORD_PROP = "") AND SQL_TRUSTED_CONNECTION="")

- edit the "Display a specific dialog" published event, which opens the next dialog, by entering the following condition:

Code: Select all

((USERNAME_PROP <> "" AND PASSWORD_PROP <> "") AND SQL_TRUSTED_CONNECTION="") OR SQL_TRUSTED_CONNECTION="1"

In this way if the Trusted Connection checkbox is checked, the user will be able to go to the following dialog without entering values in the Username and Password fields. Otherwise, if he is not using a Trusted Connection, he will be prompted by the message box if the values are missing.

2. You can evaluate the state of Trusted Connection by evaluating SQL_TRUSTED_CONNECTION property. This property is attached to the checkbox on the SQLServerConnectionDlg. When checked, SQL_TRUSTED_CONNECTION will receive "True" value.

Best regards,
Sorin

[yusuf]

Hi,
Your response gave me some headway but did not quite answer my question.

Yes I can check SQL_TRUSTED_CONNECTION property to figure out if trusted connection is checked. How to I modify 'Launch Installed File' Command line based on SQL_TRUSTED_CONNECTION property. Here is what I want to achieved,

If SQL_TRUSTED_CONNECTION property = true then my command line is /action:Publish /SourceFile:[V8DB_Dir]Vision8.dacpac /TargetServerName:[SERVER_PROP] /TargetDatabaseName:[DATABASE_PROP]
else it will use user name and password /action:Publish /SourceFile:[V8DB_Dir]Vision8.dacpac /TargetServerName:[SERVER_PROP] /TargetDatabaseName:[DATABASE_PROP] /TargetUserName[USERNAME_PROP] /TargetPass[PASSWORD_PROP]

SO, I want to add to the command line /TargetUserName[USERNAME_PROP] /TargetPass[PASSWORD_PROP] if SQL_TRUSTED_CONNECTION is true, otherwise don't include it.

Thanks

[Sorin]

Hello,

This could be achieved by using two "Launch installed File" Custom actions. For the first one enter the condition SQL_TRUSTED_CONNECTION ="1" and use the following command line:

Code: Select all

/action:Publish /SourceFile:[V8DB_Dir]Vision8.dacpac /TargetServerName:[SERVER_PROP] /TargetDatabaseName:[DATABASE_PROP]

The second custom action should have SQL_TRUSTED_CONNECTION ="0" condition and the following command line:

Code: Select all

/action:Publish /SourceFile:[V8DB_Dir]Vision8.dacpac /TargetServerName:[SERVER_PROP] /TargetDatabaseName:[DATABASE_PROP] /TargetUserName[USERNAME_PROP] /TargetPass[PASSWORD_PROP]

Best regards,
Sorin