Udostępnij za pośrednictwem


Database Project Import Scripts Add In

In this blog I’ll introduce you to the Import Scripts add-in to the General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition. With this add-in Visual Studio can import multiple scripts to populate a target database project. I’ve included the source code but, in order to compile the install project you’ll need to install Votive 3.0 (I used 3.0.4707.0).

To install run InstallImportScripts.msi (available in this zip). This install will create these files on your filesystem:

%VSINSTALLDIR%\Common7\Ide\PublicAssemblies\ImportScriptsRecursively.dll

C:\ProgramData\Microsoft\MSEnvShared\Addins\ImportScriptsRecursively.AddIn

After installing run Visual Studio 2008 and bring up the Add-in Manager by selecting Tools.Add-in Manager from the main toolbar. Verify that “Database Project Script Import” is available and selected to load.

clip_image002

A new menu will appear in the Data menu called “Import Scripts…” whenever at least one Database Project is open in the solution explorer.

clip_image004

Selecting this menu button will bring up the Scripts Selector dialog. Use this dialog to

· Select the project which will receive the imported database schema objects (the target project)

· Select the directory to search for .sql files

· Select or deselect individual files or folders from the import process. Note that, by default any scripts named Script.PostDeployment.sql, Script.PreDeployment.sql or IgnoredOnImport.sql will not be selected.

· Select the default encoding for the .sql files. If you run the addin and no database schema objects are imported verify the encoding of your files match the encoding you’ve selected in the Scripts Selector dialog.

· Choose among the various import script options available on the Import Script Wizard.

· If you choose to concatenate your scripts into one large file memory pressure will increase but performance will be vastly increased.

clip_image006

Once you select ‘Run Import’ the Output Window will be activated and messages will appear providing you with feedback on the import script process. Each file will produce output similar to the one below. The output will include 1) a progress indicator (in the case below 4/700) and 2) the DTE command which can be executed into the Command Window to re-import that particular script (in the case below Project.ImportScript /FileName "C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql" /Encoding UTF8).

Executing 4/700 : Project.ImportScript /FileName "C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql" /Encoding UTF8

Started importing file: C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql

File name C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql (size: 270)

Parsing SQL script

Total number of batches in script: 2

Total number of statements in script: 2

Finished importing file: C:\temp\AdventureWorks\AdventureWorks\Schema Objects\Database Level Objects\Security\Schemas\Person.schema.sql

Conclusion

The Import Scripts Add-in allows you to import multiple scripts at once. This should be a useful addin when upgrading from CTPs (which do not support upgrade) or importing from a script archive into a database project for the first time.

- Patrick Sirr

- DataDude Programmer

Comments

  • Anonymous
    December 01, 2008
    The comment has been removed

  • Anonymous
    July 07, 2009
    Can an option be added telling the tool to only import files specified in a particular dbproj file?  For example, in my case, we have a number of files that are under source control, but no longer part of a database solution.  I want to exclude those files when importing into a new container.   Many of our projects have hundreds of files and its difficult to distinquish between files that should and should not be included during the import.

  • Anonymous
    July 07, 2009
    Not at this time.  You'd have to snap my source code and add this option yourself.  It should be fairly straightforward to load the project file and grab the 'Build' elements and their include path.