EXTERNAL DATA SOURCE should be created before views and procedures

[asos-martinsmith]

• SqlPackage or DacFx Version:
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):

Unsure - using publish in latest version of VS 2022

VisualStudio.17.Release/17.8.4+34408.163 SQL Server Data Tools 17.8.120.1

Steps to Reproduce:

1. Have the project contain an external data source - for example

CREATE EXTERNAL DATA SOURCE [MyExternalDataSource]
    WITH (
    LOCATION = N'https://MyBlobaccountname.blob.core.windows.net',
    CREDENTIAL = [ManagedIdentityCredential]
    );

2. Have a view or stored procedure reference that data source in OPENROWSET

SELECT PARQUET_DATA.SKUID FROM OPENROWSET( BULK 'mypath*.parquet', DATA_SOURCE = 'MyExternalDataSource', FORMAT = 'PARQUET') WITH (Foo bigint)
PARQUET_DATA

3. Publish the project to a Synapse Serverless database that has neither of these objects.

The Publish script creates the external data source at the end but the deployment has already failed before that point as it tries to create the proc/view that references the non existent data source and the SQL Serverless throws an error about that.

Probably the placement of the data source creation should always happen before the procedures and views are created (as they can be dependent on the data source but the reverse isn't possible)

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[ssreerama]

Hi @asos-martinsmith,

I 'm not able to repro the issue as mentioned, always generating the correct order of the scripts where my project has Master Key, DataSource, a view and couple of tables. View is always created after creating the ExternalDataSource. Attaching my sample project Database2.zip, please test it and update me what I'm missing here.

Also, it would be very helpful if you can provide a sample repro project. Thanks

[asos-martinsmith]

Hi @asos-martinsmith,

I 'm not able to repro the issue as mentioned, always generating the correct order of the scripts where my project has Master Key, DataSource, a view and couple of tables. View is always created after creating the ExternalDataSource. Attaching my sample project Database2.zip, please test it and update me what I'm missing here.

Also, it would be very helpful if you can provide a sample repro project. Thanks

This issue was submitted ~8 months ago so maybe is already fixed by now, Does the code have logic to provide the correct ordering? If so when was that added?

[ssreerama]

@asos-martinsmith , we do have the logic for dependency order. This could get fixed with other change. Could you please test it using your initial reproduction steps and let us know if the issue persists or if it has been resolved? This will help us determine whether to continue working on it or close the issue. Thanks

[ssreerama]

HI @asos-martinsmith , Any update on the ask? Thanks

[llali]

we cannot repro this issue anymore so probably has been fixed before