How to: Import Database Scripts to Data-tier Applications

  • Article

Important

To create and manage databases using the latest version of Data Tier Applications (DACPAC), install the most recent release of SQL Server Data Tools from Get Started with Microsoft SQL Server Data Tools.

In addition to importing an existing data-tier application (DAC) package, you can also import database objects from one or more existing scripts. You could use this approach, for example, to take an existing database definition that was created by using other third-party tools and import it into your DAC project. The script that you specified is parsed, and any statements that create database objects are identified and imported into the DAC project. You can import scripts by using the Import SQL Script File wizard or by using an automation command from the Command Window.

If any object definition contains an error, the object is skipped. If the error is related to the type definition, the statement is put into the ScriptsIgnoredOnImport.sql file in the database project. If the object type is valid but the definition of the object contains an error, such as a view that references a non-existent table, an error message appears in the Error List window.

You can only import objects that are supported in DAC projects. For more information about a list of the supported objects, see Features Supported in Data-tier Applications.

Statements that are not imported are placed into the ScriptsIgnoredOnImport.sql file in your solution.

Importing Database Object Definitions

To use the Import SQL Script File wizard

  1. On the Project menu, click Import Script.

    The Import SQL Script File wizard appears.

    Note

    You can also right-click your DAC project in Solution Explorer or Schema View, and click Import Script.

  2. Click Next to advance to the Select File page after you read the introductory page of the wizard.

  3. If you want to import multiple scripts, go to step 7.

  4. Click Single file.

  5. In File name, type the file name, including the path, of the script that you want to import. You can also click Browse to find the file.

  6. Go to step 11.

  7. Click Multiple files.

  8. Click Browse and navigate to the folder that contains the folder that contains the scripts that you want to import.

    Important

    You are selecting the folder that contains the script that you want to import, not the scripts themselves.

  9. Click Select Folder.

  10. In the list of files and folders, select the check boxes that correspond to the scripts or sub-folders that contain scripts that you want to import.

  11. Click Next.

  12. If you want imported objects to replace objects in the DAC project that have the same name and type, select the Overwrite objects that already exist in the project check box.

  13. If you do not want to import extended properties, clear the Import extended properties check box.

  14. If you want to import permissions, select the Import Permissions check box.

  15. If you want to import permissions and add them to the project mode, you must also select the Add imported permissions to the project model script check box.

    Note

    If you import permissions and add them to the model, the amount of time required to open the DAC project might increase significantly.

  16. In Encoding, click the encoding with which the script file was created.

  17. Click Finish to import database object definitions from the script that you specified.

    A progress page appears as the script is parsed and any object definitions are added to your DAC project. A log file is created when you import database definitions from a script. Log files are stored in the Import Script Logs subfolder within the project folder.

    Note

    The source file is not modified and opens in shared read-only mode.

To use the Visual Studio automation model

  1. Either create or open a DAC project.

    For more information, see How to: Create a Data-tier Application Project.

  2. Open the View menu, point to Other Windows, and click Command Window.

  3. In the Command Window, type the following command:

    Project.ImportScript /FileName "MyScript.sql"

    Replace MyScript.sql with the name of the script that you want to import.

    Note

    You can specify additional options for the command that control the import operation. For more information, see Automation Command Reference for Database Features of Visual Studio Team System. If you do not specify a file name, the Import Database Wizard appears.

    The objects and settings are imported from the specified script into the project that was selected in Solution Explorer when you executed the command. The Error List window displays any errors that relate to the objects that are created when the script is imported.

    At this point, you must configure your DAC project settings before you can build and deploy your DAC project. For more information, see How to: Create a Data-tier Application Project.

Issues and Limitations

The Import SQL Script File wizard searches the specified script for all Data Definition Language (DDL) CREATE statements and adds the corresponding objects to your projects. The following limitations apply:

  • Statements that are not recognized are put in the ScriptsIgnoredOnImport.sql file in the database project.

  • Each DAC creates all of its objects in a single database. The Import SQL Script File wizard enforces this as it processes the scripts. All objects are imported into your DAC project. If the script contains the definitions for multiple databases, the CREATE DATABASE and USE statements are ignored, and all objects are added to your DAC project. If you import a script that has multiple database definitions into a single DAC project, you might receive a significant number of errors.

  • If the script contains a CREATE statement that is followed by a DROP statement for the created object, the object will be imported.

  • Only ALTER TABLE statements that add constraints to a table or specify columns for a full-text index are imported. For constraints, only statements that add a single constraint are understood. Statements that add multiple constraints to a table are skipped.

  • Batches must be separated by GO statements.

    Note

    To import the result of running the script, rather than the CREATE statements that are contained in the script, you can run the script to create a database and then use the Extract Data-tier Application Wizard in SQL Server Management Studio to extract a DAC package from the resulting database. You can then import the DAC package into your DAC project.