Error formatting a string: Input string was not in a correct format..

[bryangeiger]

Seems like I am missing something. I have re-written this a couple of times to make sure creds, connection, and scripts are correct. But once I go to Install-DBOPackage it fails every time. I have tried using connection string, tried using username and password combo, tried creds only and I get the same error. I have tried plain text password/creds and PSCredential password/creds.

Requirements: Sql Auth creds

Sample script:

cls
if(-not (Get-Module dbops -ListAvailable)){
    Install-Module -Name dbops -RequiredVersion 0.6.4 -Force
}
if(-not (Get-Module dbatools -ListAvailable)){
    Install-Module -Name dbatools -RequiredVersion 1.0.145 -Force
}
if(-not (Get-Module PSFramework -ListAvailable)){
    Install-Module -Name PSFramework -Force
}
if(-not (Get-Module ziphelper -ListAvailable)){
    Install-Module -Name ziphelper -Force
}

$path = Get-Location
$user = 'user'
$pass = 'pass'
$server = 'server'
$db = 'db'
$config = Get-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip" | Select-Object -ExpandProperty Configuration

$config.SqlInstance = $server
$config.Database = $db
$config.Username = $user

$password = ConvertTo-SecureString $pass -AsPlainText -Force
$config.Password = $password

#tried this as well, same error
#$credential = New-Object System.Management.Automation.PSCredential ($user, $password)
#$config.Credential = $credential

$config.SaveToFile('.\dev.json')
Get-Content .\dev.json

Install-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip" -Configuration .\dev.json -Verbose

Error Message

[07:52:51][Install-DBOPackage] Upgrade failed due to an unexpected exception:
System.Management.Automation.RuntimeException: Error formatting a string: Input string was not in a correct format..
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at WriteError(Closure , FunctionContext )
   at System.Management.Automation.ScriptBlock.InvokeWithPipeImpl(ScriptBlockClauseToInvoke clauseToInvoke, Boolean createLocalS
cope, Dictionary`2 functionsToDefine, List`1 variablesToDefine, ErrorHandlingBehavior errorHandlingBehavior, Object dollarUnder,
 Object input, Object scriptThis, Pipe outputPipe, InvocationInfo invocationInfo, Object[] args)
   at System.Management.Automation.ScriptBlock.<>c__DisplayClass57_0.<InvokeWithPipe>b__0()
   at System.Management.Automation.Runspaces.RunspaceBase.RunActionIfNoRunningPipelinesWithThreadCheck(Action action)
   at System.Management.Automation.ScriptBlock.InvokeWithPipe(Boolean useLocalScope, ErrorHandlingBehavior errorHandlingBehavior
, Object dollarUnder, Object input, Object scriptThis, Pipe outputPipe, InvocationInfo invocationInfo, Boolean propagateAllExcep
tionsToTop, List`1 variablesToDefine, Dictionary`2 functionsToDefine, Object[] args)
   at System.Management.Automation.ScriptBlock.InvokeAsMemberFunction(Object instance, Object[] args)
   at DbUp.SqlServer.SqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action excuteCommand
)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_0.<Execute>b__0(Func`1 dbCommandFactory)
   at DbUp.Support.ScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()

SourcePath  : {D:\gn\db-crm\package\sqlscripts\DBOPackage.zip}
SqlInstance : server
Database    : db
Successful  : False
Error       : System.Management.Automation.RuntimeException: Error formatting a string: Input string was not in a correct 
              format..
                 at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, 
              Exception exception)
                 at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                 at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                 at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
Duration    : 00:00:00.2811922

Error formatting a string: Input string was not in a correct format..
At C:\Program Files\WindowsPowerShell\Modules\dbops\0.6.4\internal\classes\DBOpsLog.class.ps1:63 char:9
+         $splatParam = @{
+         ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Data.Sql...tate:1,Class:16:String) [], RuntimeException
    + FullyQualifiedErrorId : FormatError

[nvarscar]

I imagine you have a need for the configuration to be stored in a config file, otherwise all of those parameters can be passed directly to the Install-DBOPackage function without the need to store this in a file.

From what I can see in the error log, the error seems to be happening during the PerformUpgrade() ScriptExecutor.Execute() phase, at which point the connection object should already exist and has checked whether there were any scripts executed in this database already. So we can assume the connectivity or password is not a problem here. Can you attach the first sql file it is about to run? Maybe there's some unexpected parsing issue caused by the DbUp framework.

[nvarscar]

PS you can use the following syntax too if you want

$config = Get-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip" | Get-DBOConfig -Configuration @{
  User = 'foo'
  Password = ConvertTo-SecureString 'bar' -AsPlainText -Force
  ...
}

This would grab the configuration from the package object and merge it with the values from the hashtable.

[bryangeiger]

Wow, thank you for the quick response, very impressive. Below is the only script I am attempting to execute in the package.

In regards to your second comment, I tried that before but I will try again to make sure.

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseInventory]') AND type IN (N'U'))
BEGIN 
/* Create a database table for storing servers */
 CREATE TABLE [dbo].[DatabaseInventory] (
     [ServerName] NVARCHAR(60) NOT NULL,
     [Description] NVARCHAR(60) NULL
     )

 End

[bryangeiger]

Tried your suggestion, same error, below is a simplified version.

cls
$path = 'D:\GN\db-crm\'
$user = 'user'
$pass = 'pass'
$server = 'server\instance' #tried {{server}},{{port}} & {{server}}\{{instnace}}
$db = 'db'

Invoke-DBOPackageCI -Path "$path\package\sqlscripts\DBOPackage.zip" -ScriptPath "$path\package" -Version 1.0 

$config = Get-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip" | Get-DBOConfig -Configuration @{
  SqlInstance = $server
  Database = $db
  Username = $user
  Password = ConvertTo-SecureString $pass -AsPlainText -Force
}
$config.SaveToFile('.\dev.json')
Get-Content .\dev.json

Install-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip" -Configuration .\dev.json -Verbose

[nvarscar]

Hmm, does the zip file exist by the time you run Invoke-DBOPackageCI? If so, by using a parent path as a -ScriptPath parameter, you're adding a zip file as a sql file, which would eventually result in failure to format it.

You can check which files are included by running

$package = Get-DBOPackage -Path "$path\package\sqlscripts\DBOPackage.zip"
$package.Builds.Scripts.Name