Importing Multiple Scripts using DTE

We have had a lot of requests from folks that would rather not go through the Import Script Wizard when they have multiple T-SQL source scripts. Fortunately Visual Studio provides the ability to execute parameterized commands by using the Design Time Extensibility (DTE) framework. For a typical list of such commands visit this MSDN Library entry. In this blog I’ll lead you through a useful command provided by General Distribution Release (GDR) for Visual Studio Team System 2008 Database Edition which allows you to bypass the Import Script Wizard and import your scripts directly into your project system.

Step 1: Create a script

Create a script as c:\myScript.txt with the following contents:

CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) ) GO EXEC sp_addextendedproperty @name='TableWithComputedColumn', @value ='column_3', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Table1', @level2type = NULL, @level2name = NULL GO CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1] GO CREATE USER [User1] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo; GO GRANT UPDATE ON [dbo].[Table1] TO [User1]

Create another script “c:\myScript2.txt” with the following contents:

CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0

Step 2: Create a Project

Launch Visual Studio and create a SQL Server 2008 project:

Step 3: Select the new project in the Solution Explorer

When you execute DTE commands, they execute in the context of the project that is selected in the Solution Explorer. You should back up your project or, better yet, check in your project to version control before you run this DTE command.

Step 4: Display the Command Window

By using the Command Window, you can run DTE commands and provide parameters to those commands that allow them. To display the Command Window, open the View menu, point to Other Windows, and click Command Window.

Step 5: Import the Script

The import script DTE command is “Project.ImportScript”. Now you are at the point where you can run the command and have some fun.

First try the command without any parameters (“Project.ImportScript”):

You’ll notice that the Import Script wizard appears. This is the command that is executed when you right-click on the project node and click “Import Script”. Not so interesting. Dismiss this dialog box. Next you will parameterize the command.

The parameters for “Project.ImportScript” are as follows.

Project.ImportScript /FileName [filename] [/encoding Unicode|UTF32|UTF8|UTF7] [/Overwrite] [/IgnoreExtendedProperties] [/IgnorePermissions] [/AddImportedPermissionsToModel]

/FileName [filename]	This is the file that you want import. Place quotes around the file name if it has embedded spaces.
/encoding Unicode|UTF32|UTF8|UTF7	The encoding of the file
[/Overwrite]	If duplicate objects are discovered should they be overwritten?
[/IgnoreExtendedProperties]	Suppress importing extended properties
[/IgnorePermissions]	Suppress importing permissions
[/AddImportedPermissionsToModel]	If provided, any imported permissions will not be added to the model. This means your permissions XML file will have a Build Action of “Not In Build”

Now import your myScript.sql file. You can ignore extended properties and permissions. Run the script below (“Project.ImportScript /FileName “C:\myScript.sql” /IgnoreExtendedProperties /IgnorePermissions”):

Notice that your project now has the following files:

MyProject\Schema Objects\Schemas\dbo\Tables\Table1.table.sql

CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) )

MyProject \Schema Objects\Schemas\dbo\Views\View1.view.sql

CREATE VIEW [dbo].[View1] AS SELECT column_3 FROM [dbo].[Table1]

If you now change the command to “Project.ImportScript /FileName "C:\myScript.sql" /Overwrite /IgnorePermissions” the table script will be altered to:

CREATE TABLE [dbo].[Table1] ( column_1 int NOT NULL, column_2 int NULL, column_3 as (column_1 * 14.3) ) GO EXEC sp_addextendedproperty @name='TableWithComputedColumn', @value ='column_3', @level0type = 'SCHEMA', @level0name = 'dbo', @level1type = 'TABLE', @level1name = 'Table1', @level2type = NULL, @level2name = NULL

To import your second script, change the command in the Command Window to ““Project.ImportScript /FileName "C:\myScript2.sql”. After you run the command, the project system contains the following file:

MyProject \Schema Objects\Schemas\dbo\Programmability\Stored Procedures\Procedure1.proc.sql

CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0

Obviously you can now repeat this process for all the SQL files that make up your schema.

Conclusion

Note that any DTE command is available through DTE automation and is accessible through EnvDTE80.DTE2.ExecuteCommand or EnvDTE80.DTE2.Commands.Raise. You can use the DTE commands to create Add-Ins, external processes, or packages to automate your specific system. GDR for Visual Studio Team System 2008 Database Edition GDR provides other parameterized commands. I will blog about these soon.

 

- Patrick Sirr

“DataDude” Programmer

Comments

• Anonymous
  November 22, 2008
  Patrick Sirr , one of the key developers in our team started his own blog. If you want to learn about

• Anonymous
  November 25, 2008
  Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition

• Anonymous
  November 25, 2008
  The VS Database edition team has just released the golden version of the Visual Studio Team System 2008

• Anonymous
  November 25, 2008
  The VS Database edition team has just released the golden version of the Visual Studio Team System 2008

• Anonymous
  November 18, 2009
  The pictures no longer show.  Would you please restore them.  Clicking on them leads to a 404 message about: http://patrick.sirr.members.winisp.net/blog/b388cb959fca_13689/clip_image006.jpg thanks

• Anonymous
  November 18, 2009
  Restored!  Thanks for letting me know.  Patrick

• Anonymous
  November 18, 2009
  This works fine, one at a time.  I've got a file of 340 command to run.  is there a way to run them without pasting each one?   Thanks Andy

• Anonymous
  November 18, 2009
  You have a bunch of options.  You could create a macro, addin or even drive Visual Studio from an external package.  If you're importing multiple scripts you might want to try my addin here: http://blogs.msdn.com/psirr/archive/2008/11/24/database-project-import-scripts-add-in.aspx