20 Dec 2006

MSBuild using SQL Script parameters

I have been working with MSBuild and the Microsoft.Sdc.Tasks to deploy my current project’s databases.  Some of the SQL scripts that are executed within MSBuild need parameters to be set to create the database objects, which the Microsoft.Sdc.Tasks.Sql.Execute method supports.  To use parameters within MSbuild and SQL.Execute; firstly create an item group (<ItemGroup>) then create element of any name as this will become the name of the item that can be referred to in MSbuild.  Within the new element an attribute has to set called “Include”, then create two other elements called “name” and “value”.  Here is an example:

<ItemGroup>

            <schemaName Include=”true”>

<name>@pTableName</name>

<value>’sys.Objects’</value>

            </schemaName>

            <schemaTable Include=”true”>

<name>@pTableId</name>

<value>342</value>

            </ schemaTable >

</ItemGroup>

 

The name element needs to be set to the name of the parameter including the @.  The value element needs to be set with the value to which the parameter has to be set to. The only gotach I have found so far is that string data needs to wrapped in the quotes. 

 

Then to the get the MSBuild to pass the parameter into the Sql.Execute Task set a attribute on the task called Parameters and then @(<ItemName>).  The Parameters will take in a list of parameters.  Here is an example:  <Sql.Execute Path="CreateTable.sql" ServerName="$(SQLServer)" DatabaseName="$(Database)" Parameters="@( schemaName);@( schemaTable)" />

No comments: