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