SQLCI.ps1 is not present in installation directory

Hi,

I'm trying to setup SQL Automation on my build server TeamCity but having some issues. I have installed all tools except Source Control using SQLToolbeltEssentials executable on build server.

Which location will be used to set DLMAS_HOME environment variable on server?

I tried to set Redgate installation folder but it looks like this is not correct.

Build step is looking of SQLCI.ps1 at home directory but this file not present in any tools folder.

Please let me know where I can find it or do I need to install any more tools.
Senior DevOps Engineer
Tagged:

Comments

  • Hi,

    You need DLM Automation installed on your build server, but this isn't included in SQL Toolbelt Essentials. It is only available in the full SQL Toolbelt.
    Software Engineer
    Redgate Software
  • Hi Rob,

    Perfect, that solved my problem.

    Now when I proceeded further with my integration. I got below error in Build Log:


    [16:28:43][Step 4/7] InputObject: Database\Isango.Database
    [16:28:43][Step 4/7] TemporaryDatabaseServer: Data Source=x.x.x.x;User
    [16:28:43][Step 4/7] ID=sa;Password=********;Application Name="Redgate DLM Automation"
    [16:28:43][Step 4/7] TemporaryDatabase: null
    [16:28:43][Step 4/7] SQLCompareOptions:
    [16:28:43][Step 4/7] TransactionIsolationLevel: Serializable
    [16:28:43][Step 4/7] FilterPath:
    [16:28:43][Step 4/7] QueryBatchTimeout: 30
    [16:28:43][Step 4/7] Validating schema in scripts folder 'D:\xxx\TeamCity\buildAgent\work\734075a537fc9592\Database\Isango.Database'.
    [16:28:43][Step 4/7] WARNING: No 'RedGateDatabaseInfo.xml' file located at the root of the scripts
    [16:28:43][Step 4/7] folder
    [16:28:43][Step 4/7] 'D:\xxx\TeamCity\buildAgent\work\734075a537fc9592\Database\Isango.Database'.
    [16:28:43][Step 4/7] Some features, such as static data, may not work as expected.
    [16:28:43][Step 4/7] Validating schema in scripts folder 'D:\xxx\TeamCity\buildAgent\work\734075a537fc9592\Database\Isango.Database'.
    [16:28:43][Step 4/7] WARNING: No 'RedGateDatabaseInfo.xml' file located at the root of the scripts
    [16:28:43][Step 4/7] folder
    [16:28:43][Step 4/7] 'D:\xxx\TeamCity\buildAgent\work\734075a537fc9592\Database\Isango.Database'.
    [16:28:43][Step 4/7] Some features, such as static data, may not work as expected.
    [16:28:43][Step 4/7] VERBOSE: Using temporary database on server 'xx.xx.xx.xx'.
    [16:28:43][Step 4/7] VERBOSE: Dropping temporary database
    [16:28:43][Step 4/7] 'dlmautomation_843c3ff3-7751-4ce8-bd6f-ee56b189b329' on 'x.x.x.x'.
    [16:28:43][Step 4/7] Invoke-DlmDatabaseSchemaValidation : SQL script failed to execute: Incorrect
    [16:28:43][Step 4/7] syntax near the keyword 'ON'.
    [16:28:43][Step 4/7] Local parameters [connectionString = Data Source=x.x.x.x;User
    [16:28:43][Step 4/7] ID=sa;Password=********;Application Name="Redgate DLM Automation"]
    [16:28:43][Step 4/7] At D:\xxx\RedGate\DLM Automation 2\SqlCi.ps1:138 char:41
    [16:28:43][Step 4/7] + ... ptsFolder | Invoke-DlmDatabaseSchemaValidation @temporaryConnection @ ...
    [16:28:43][Step 4/7] + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    [16:28:43][Step 4/7] + CategoryInfo : NotSpecified: (:) [Invoke-DlmDatabaseSchemaValid
    [16:28:43][Step 4/7] ation], TerminatingException
    [16:28:43][Step 4/7] + FullyQualifiedErrorId : SqlExecutionError,RedGate.DLMAutomation.PowerShe
    [16:28:43][Step 4/7] ll.Commands.InvokeDlmDatabaseSchemaValidationCommand
    [16:28:43][Step 4/7] D:\xxx\TeamCity\buildAgent\plugins\dlmautomation-teamcity-agent\DLMAutomatio
    [16:28:43][Step 4/7] nRunner.ps1 : Error running DLM Automation action: see build log for error
    [16:28:43][Step 4/7] details
    [16:28:43][Step 4/7] + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorExcep
    [16:28:43][Step 4/7] tion
    [16:28:43][Step 4/7] + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio
    [16:28:43][Step 4/7] n,DLMAutomationRunner.ps1
    [16:28:43][Step 4/7]
    [16:28:46][Step 4/7] Process exited with code 1
    [16:28:46][Step 4/7] Process exited with code 1
    [16:28:46][Step 4/7] Step Database Build (Redgate DLM Automation Build) failed

    Can you please let me know how to generate RedGateDatabaseInfo.xml file for my database or which tool will be used for this purpose?

    and is this the only reason for failed build or any other?

    Thanks,
    Imran
    Senior DevOps Engineer
  • Hi Imran,

    DLM Automation is designed to build a scripts folder that has been generated with SQL Source Control. This should have the RedGateDatabaseInfo.xml file automatically present. How did you create the scripts folder that you are trying to build?
    Software Engineer
    Redgate Software
  • Hi Rob,

    I recreated Script folder using SQL Compare Tool but the issue is still same. still getting same error.

    [11:22:23][Step 4/7] 'dlmautomation_450ea24c-8598-45e7-b944-252a5d103f45' on 'x.x.x.x'.
    [11:22:23][Step 4/7] Invoke-DlmDatabaseSchemaValidation : SQL script failed to execute: Incorrect
    [11:22:23][Step 4/7] syntax near the keyword 'ON'.
    [11:22:23][Step 4/7] Local parameters [connectionString = Data Source=x.x.x.x;User
    [11:22:23][Step 4/7] ID=sa;Password=********;Application Name="Redgate DLM Automation"]
    [11:22:23][Step 4/7] At D:\xxx\RedGate\DLM Automation 2\SqlCi.ps1:138 char:41
    [11:22:23][Step 4/7] + ... ptsFolder | Invoke-DlmDatabaseSchemaValidation @temporaryConnection @ ...
    [11:22:23][Step 4/7] + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    [11:22:23][Step 4/7] + CategoryInfo : NotSpecified: (:) [Invoke-DlmDatabaseSchemaValid
    [11:22:23][Step 4/7] ation], TerminatingException
    [11:22:23][Step 4/7] + FullyQualifiedErrorId : SqlExecutionError,RedGate.DLMAutomation.PowerShe
    [11:22:23][Step 4/7] ll.Commands.InvokeDlmDatabaseSchemaValidationCommand
    [11:22:23][Step 4/7] D:\DevOps\TeamCity\buildAgent\plugins\dlmautomation-teamcity-agent\DLMAutomatio
    [11:22:23][Step 4/7] nRunner.ps1 : Error running DLM Automation action: see build log for error
    [11:22:23][Step 4/7] details
    [11:22:23][Step 4/7] + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorExcep
    [11:22:23][Step 4/7] tion
    [11:22:23][Step 4/7] + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio
    [11:22:23][Step 4/7] n,DLMAutomationRunner.ps1
    [11:22:23][Step 4/7]
    [11:22:25][Step 4/7] Process exited with code 1
    [11:22:25][Step 4/7] Process exited with code 1
    [11:22:25][Step 4/7] Step Database Build (Redgate DLM Automation Build) failed
    Senior DevOps Engineer
  • Is this still some setup issue or it can be some error in script? How can I get more detailed error log to look where exactly this issue is happening?
    Senior DevOps Engineer
  • Sergio RSergio R Posts: 578 Rose Gold 5
    edited June 6, 2017 11:34AM
    Hi Imran,

    You can use SQL Compare Pro to compare and sync the scripts folder to an empty DB, this is likely to give you more information regarding the error you are getting.

    Thank you,
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • This issue is solved now and build is completed successfully. I'm now having other issue with Automation Sync step in TeamCity which I'll post in another thread.
    Senior DevOps Engineer
Sign In or Register to comment.