To deploy a .bacpac file Amazon RDS MS SQL Server 2022

0

Hi AWS, I was trying to deploy a CI/CD pipeline for MS SQL 2022 using GitHub Actions on Amazon RDS. Here is the code repo: https://github.com/arjungoel/sql-cicd-poc. Having said that I was trying to create a .dacpac file and then deploying it on the Target Server. However I figured out that only the CREATE TABLE commands were working and apart from that no other DML and DDL commands like INSERT, UPDATE, ALTER are working as I came to know only I got this post from stackoverflow https://stackoverflow.com/questions/19819681/how-to-include-data-in-a-dacpac.

This is the sql code for database table:

CREATE TABLE [dbo].[Bank]
(
    [BankName] [nvarchar](50) NOT NULL,
    [BankAddress] [nvarchar](200) NOT NULL,
    [BankIFSCCode] [nvarchar] (100) NOT NULL
)

GO

Insert into Bank Values ('sbi2', 'naraina2', 'ifsc_code2'), ('sbi3', 'naraina3', 'ifsc_code3'), ('sbi4', 'naraina4', 'ifsc_code4');

GO

But when I ran the Export command to create a .bacpac file as it contains both data and structure and the command is:

SqlPackage /Action:Export /TargetFile:"C:\database-projects\sql-cicd-poc\output.bacpac" /SourceConnectionString:"Server=tcp:EC2AMAZ-DC3KDV1,1433;Initial Catalog=bank;Persist Security Info=False;User ID=sqldemopoc;Password=sqldemo1234@#;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;"

it creates the .bacpac file but it doesn't have the table inside. You can refer the screenshot below:

bacpac file output

You can see it is missing _rels and Data directory. Is it because the tables have not been created and I need to add one more job just to have that table structure in place (.dacpac) file.

Furthermore I ran these commands:

SqlPackage /Action:Extract /TargetFile:result.dacpac /p:ExtractAllTableData=true /p:VerifyExtraction=true /SourceServerName:EC2AMAZ-DC3KDV1 /SourceDatabaseName:bank /SourceUser:sqldemopoc /SourcePassword:sqldemo1234@# /sec:false --> to create .dacpac file

SqlPackage /Action:Publish /SourceFile:"C:\database-projects\sql-cicd-poc\result.dacpac" /TargetConnectionString:"Server=tcp:EC2AMAZ-DC3KDV1,1433;Initial Catalog=bank;Persist Security Info=False;User ID=sqldemopoc;Password=sqldemo1234@#;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" --> to deploy the changes on the Target Server. It gave me this message with a warning:

Publishing to database 'bank' on server 'tcp:EC2AMAZ-DC3KDV1,1433'. Initializing deployment (Start) *** The object [sqldemopoc] already exists in database with a different definition and will not be altered. Initializing deployment (Complete) Analyzing deployment plan (Start) Analyzing deployment plan (Complete) Updating database (Start) Update complete. Importing data Disabling indexes. (Start) Disabling indexes. (Complete) Processing Import. 0.00% done. Processing Import. 100.00% done. Processing Table '[dbo].[Bank]'. 100.00% done. Enabling indexes. (Start) Enabling indexes. (Complete) Importing data Updating database (Complete) Successfully published database. Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection Time elapsed 0:00:09.59

My mind is blown away so I need a way to figure out things in the sequence:

  1. To create a build (.dacpac or .bacpac) which could have both structure and data in place and for that I need to run two jobs, YES or NO?
  2. Once done what are the series of commands which I need to run in sequence.

Please guide.

  • One more question I have why the DDL and DML commands (INSERT, DELETE, UPDATE, ALTER, DROP) etc. are not getting triggered using CI/CD pipeline for both .bacpac and .dacpac. I was expecting the changes would be triggered when exporting the data to .bacpac file but it is not true for the databases which has no tables and records.

1 Answer
0

I would try testing with the following option /p:TableData=<table name>

Please mark this as the accepted answer if it is helpful in extracting the specific table, so that the re:Post community may benefit. Thank you!

profile pictureAWS
answered a month ago
  • Hi AWS-Eric, I used these two versions of the command and got these errors:

    1. ** SqlPackage /Action:Extract /TargetFile:result.dacpac /p:ExtractAllTableData=true /p:VerifyExtraction=true /SourceServerName:EC2AMAZ-DC3KDV1 /SourceDatabaseName:bank /SourceUser:sqldemopoc /SourcePassword:sqldemo1234@# /p:TableData=bank.BankInfo /sec:false**

    ERROR: Connecting to database 'bank' on server 'EC2AMAZ-DC3KDV1'. *** Error extracting database:Argument 'ExtractAllTableData' has an invalid value:'true'. 'ExtractAllTableData' cannot be 'true' because individual user tables are also specified using the 'TableData' argument. Set 'ExtractAllTableData' to 'false' to extract data from the specified user tables. Time elapsed 0:00:00.17

    1. SqlPackage /Action:Extract /TargetFile:result.dacpac /p:ExtractAllTableData=false /p:VerifyExtraction=true /SourceServerName:EC2AMAZ-DC3KDV1 /SourceDatabaseName:bank /SourceUser:sqldemopoc /SourcePassword:sqldemo1234@# /p:TableData=bank.BankInfo /sec:false

    ERROR: Connecting to database 'bank' on server 'EC2AMAZ-DC3KDV1'. Extracting schema Extracting schema from database Resolving references in schema model Validating schema model Validating schema model for data package Validating schema *** Error extracting database:Table with schema "bank" and name "BankInfo" was specified for data import or export, but does not exist in the database schema. Time elapsed 0:00:02.82

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions