SQL script in BTDF

Topics: General Questions
Jan 24, 2011 at 5:23 AM

How to deploy SQL script using BTDF.

Could you give me a clear picture of it?

Thanks in Advance.

Coordinator
Jan 24, 2011 at 6:15 AM

There is no built-in support for running SQL scripts.  However, you can create a <Target Name="CustomPostDeployTarget"></Target> in your .btdfproj, and within that include an <Exec> MSBuild task to call osql.exe or sqlcmd.exe, whatever is appropriate for your version of SQL Server.  To include the SQL script itself into your MSI, you can create an ItemGroup with AdditionalFiles elements (same format as Schemas, Orchestrations, etc.).

Thanks,
Tom

Jan 24, 2011 at 6:52 AM

Do i need to add anything to the environmental settings file

Thanks.

mala.

Coordinator
Jan 24, 2011 at 7:00 AM

Not to do the basic form of what I described, but if you need to pass a SQL Server name to osql/sqlcmd and the name is different in each environment (and if you want to run the script in more than just your production environment), then you might use the settings spreadsheet.

If you want to have a parameter value available that is different in each environment, you would:

  1. Add a row to the settings spreadsheet and fill in the value for each environment (like the server name)
  2. In .btdfproj, locate the <PropsFromEnvSettings> property and add to it the name of your setting (first column of the spreadsheet), semicolon delimited.  That creates an MSBuild property that holds the value from the spreadsheet.
  3. Reference the setting name as an MSBuild property where needed, as $(TheSettingName)

Tom

Jan 24, 2011 at 8:00 AM

fine thats clear,i will try it out.

-----------------------------------------------------

<ItemGroup>
       <AdditionalFiles Include="SQLQuery.sql">
           <LocationPath>E:\Malathi\BTDFProject\BTDFProject</LocationPath>
        </AdditionalFiles>
  </ItemGroup>

   
   
  <Import Project="$(DeploymentFrameworkTargetsPath)BizTalkDeploymentFramework.targets" />

    <Target Name="CustomDeployTarget">

        <Exec Command="sqlcmd.exe &quot;SQLQuery.sql&quot;" />

    </Target>

 

-----------------------------------------------

does the script i wrote to excecute sqlcmd.exe is correct?

Jan 24, 2011 at 11:38 AM

i got the sql script executed.

thanks,

Mala.

Coordinator
Jan 24, 2011 at 5:25 PM

Great.  One observation: avoid using explicit paths in your ItemGroups, because they will only work on your computer.  You can use relative paths -- the starting location will be always be the Deployment project folder.  Normally ".." will get to your solution root.

Thanks,
Tom

Jan 25, 2011 at 4:19 AM

Yes, Tom i will use relative path instead of explicit path.

Thanks,

Mala.