Forum Discussion

AnmolGan81's avatar
AnmolGan81
Advocate II
3 months ago
Solved

Publishing into Target Warehouse using VS Code causing issues

I am trying to publish existing warehouse to another target warehouse using VS Code, when I try to publish it I get below error, some of my tables are using dynamic sql related to INFROMATION_SCHEMA....
data warehouse
Warehouse
  • v-moharafi-msft's avatar
    v-moharafi-msft
    3 months ago

     

    Hi AnmolGan81 ,

     

    Thanks for your Patience.

     

    Yes, post-deployment scripts can be used as part of the SQL Database Project deployment flow in a DevOps pipeline. In this scenario, they are particularly useful because the issue is occurring during design-time validation rather than during runtime execution inside the Fabric Warehouse.

    A practical workaround is to separate the main schema deployment from the stored procedures containing dynamic SQL or unsupported dependency references.

    Edit the .sqlproj file directly and add a <PostDeploy> item inside the <ItemGroup> section:

    <ItemGroup>
    <PostDeploy Include="scripts\after-script.sql" />
</ItemGroup>

Similarly, pre-deployment scripts can be added using:

<ItemGroup>
    <PreDeploy Include="scripts\before-script.sql" />
</ItemGroup>

    When the SQL project is built, these scripts are packaged into the generated DACPAC. During deployment through the DevOps pipeline using tools such as SqlPackage or DACPAC deployment tasks, the execution order is handled automatically:

    1.Pre-deployment script
    2. Schema deployment/publish
    3. Post-deployment script

    Since post-deployment scripts execute after schema publish completes, they may help bypass the dependency validation failures occurring during the build/publish phase.

    Hopefully, separating the procedures containing dynamic references from the primary schema deployment helps resolve the deployment validation issue in your scenario

     

    For more information refer to the documentation:

    add-pre-deployment-and-post-deployment-scripts 

    Best Regards,
    Abdul Rafi

     

v-moharafi-msft's avatar
v-moharafi-msft
Community Support
3 months ago

Hi AnmolGan81,

 

Thank you for validating the behaviour through your testing.

 

This appears to be related to the SQL Database Project build and publish validation process rather than the runtime execution of the stored procedures themselves.

 

Microsoft documentation states that SQL Database Projects validate object references and dependencies during the build phase before generating and publishing the dacpac. The documentation also notes that system objects are not included in the project model by default, which can lead to validation errors for references to system objects during build and publish validation.

 

In your scenario, dynamic SQL references involving sys.sp_executesql and INFORMATION_SCHEMA.COLUMNS appear to be contributing to the dependency validation failure even though those objects are available at runtime.

Since deployment succeeds when the affected procedures are excluded, but fails when they are included, this seems related to the current dependency validation handling for certain dynamic SQL patterns in Fabric Warehouse SQL Projects rather than deployment order or stored procedure logic itself.

 

As a possible workaround, you could try moving the affected procedures into post-deployment scripts or simplifying the dynamic SQL temporarily to isolate the specific dependency reference causing the validation failure.

 

Relevant Microsoft documentation:

SQL Database Projects Overview
https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects

Database References
https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/database-references

Pre/Post Deployment Scripts
https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/pre-post-deployment-scripts

 

Best Regards,
Abdul Rafi

 

AnmolGan81's avatar
AnmolGan81
Advocate II
3 months ago

Since our main objective is to deploy those objects using a devops pipeline can you help is in the right direction on how can we deploy post deployment script from devops pipeline into a warehouse?

  • v-moharafi-msft's avatar
    v-moharafi-msft
    Community Support
    3 months ago

     

    Hi AnmolGan81 ,

     

    Thanks for your Patience.

     

    Yes, post-deployment scripts can be used as part of the SQL Database Project deployment flow in a DevOps pipeline. In this scenario, they are particularly useful because the issue is occurring during design-time validation rather than during runtime execution inside the Fabric Warehouse.

    A practical workaround is to separate the main schema deployment from the stored procedures containing dynamic SQL or unsupported dependency references.

    Edit the .sqlproj file directly and add a <PostDeploy> item inside the <ItemGroup> section:

    <ItemGroup>
    <PostDeploy Include="scripts\after-script.sql" />
</ItemGroup>

Similarly, pre-deployment scripts can be added using:

<ItemGroup>
    <PreDeploy Include="scripts\before-script.sql" />
</ItemGroup>

    When the SQL project is built, these scripts are packaged into the generated DACPAC. During deployment through the DevOps pipeline using tools such as SqlPackage or DACPAC deployment tasks, the execution order is handled automatically:

    1.Pre-deployment script
    2. Schema deployment/publish
    3. Post-deployment script

    Since post-deployment scripts execute after schema publish completes, they may help bypass the dependency validation failures occurring during the build/publish phase.

    Hopefully, separating the procedures containing dynamic references from the primary schema deployment helps resolve the deployment validation issue in your scenario

     

    For more information refer to the documentation:

    add-pre-deployment-and-post-deployment-scripts 

    Best Regards,
    Abdul Rafi