Can't build a project for Synapse with stored procedure with more than one "copy into... with FILE_TYPE"

[joanteixi]

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

Steps to Reproduce:

1. Create a SQL project with only this stored procedure:

CREATE PROCEDURE [dbo].[sp_MultipleCopyinto]
AS
BEGIN


COPY INTO [dbo].[test1]
		    FROM 'https://dummy.dfs.core.windows.net/raw/*.parquet'
		    WITH (
				FILE_TYPE = 'PARQUET'				    
				) ;

COPY INTO [dbo].[test2]
		    FROM 'https://dummy.dfs.core.windows.net/raw/*.parquet'
		    WITH (
				FILE_TYPE  = 'PARQUET'				    
				) ;


END

3. Build the project

I get the error: bash An item with the same key has already been added. Key: File_Type

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

I test with command line and Azure Data Studio with Database Project Extension.

[Benjin]

We think this may be fixed in the latest version. Can you try on the latest version of DacFx (16.X), please?

[joanteixi]

Hi @Benjin, could you explain how can I try with last version?? I'm running this from visual studio extension sql Database project and not sure which version of DacFX is using...

thanks for your help!!

[joanteixi]

Hi,

finally it worked... not sure how I did but...

• update the SQL Database Projects v0.18.0 preview version

Then, create a SQL project importing a Synapse Datawarehouse. And finally it's necessary to update some properties in the PROJECT.sqlproj file:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <PropertyGroup>
    <Name>PROJECTNAME</Name>
    <ProjectGuid>{03CE5201-EA80-4C36-A6C9-4436A4FED37C}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlDwDatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <OutputType>Database</OutputType>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
  <Import Condition="'$(NetCoreBuild)' == 'true'" Project="$(NETCoreTargetsPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <PackageReference Condition="'$(NetCoreBuild)' == 'true'" Include="Microsoft.NETFramework.ReferenceAssemblies" Version="1.0.0" PrivateAssets="All" />
  </ItemGroup>
</Project>

I must to confess that I'm not sure what I'm doing, but now I don't get the error ``` An item with the same key has already been added. Key: File_Type````

[joanteixi]

If I add the line

    <Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />

then the error appears.

[ErikEJ]

It is a preview...

[dzsquared]

We're mixing a few things in this issue.

You mention the Microsoft.Build.Sql SDK and copied in a SQLproj XML for the legacy style SQL projects. Which SQL project type are you wanting to use? If you're working in Visual Studio (not VS Code), then its the legacy style SQL projects only at this time. You won't be able to incorporate Microsoft.Build.Sql into the sqlproj.

If you're looking to use Microsoft.Build.Sql at the moment I'd suggest creating a SQLproj in VS Code or Azure Data Studio.

[joanteixi]

Hi... @llali could you explain to me why to closed the issue? it's resolved in some commit? I've the same issue still... (and I knw that this is a preview feature...)

[llali]

@joanteixi this issue should be fixed in DacFx 160.6296.0 But you are still seeing the issue please feel free to re-open. You won't be able to see the fix in Visual Studio just yet, but you can verify it in VsCode or Azure Data Studio as explained above. I verified it in Azure Data Studio myself and here's the result: