execute SQL scripts from BTDF

Topics: General Questions
Jun 19, 2013 at 9:37 AM
Hi Tom

Greetings! I want to run SQL scripts using bTDF. I saw some of the posts related to this. I tried to do the same way but its not working. Could you please tell me if this code below is right. I have added this in the.btdf.proj file

<ItemGroup>
<AdditionalFiles Include="SQLQuery.sql">
  <LocationPath>E:\DATA\Keshav\ALM_Build-Test_PowershellScripts</LocationPath>
</AdditionalFiles>
</ItemGroup>

<Import Project="$(DeploymentFrameworkTargetsPath)BizTalkDeploymentFramework.targets" />
<Target Name="CustomDeployTarget">
<Exec Command="sqlcmd.exe SQLQuery.sql" />
</Target>

Thanks in Advance
Jun 19, 2013 at 9:54 AM
Edited Jun 19, 2013 at 10:38 AM
I am unable to get the path of the .sql file. I have actually commented the Itemgroup tag now and directly giving the path as shown below.its working now.

But i dont want to give the path like this in "exec" tag


<Target Name="CustomDeployTarget">
<Exec Command="sqlcmd.exe -S ZEW-V-0AC2807A\ISDEV -i E:\DATA\Keshav\ALM_Build-Test_PowershellScripts\SQLQuery.sql" />
</Target>
Coordinator
Jun 19, 2013 at 4:37 PM
Hi, if you are using AdditionalFiles to include the SQL file, then your path in CustomDeployTarget should be ..\SQLQuery.sql. The current directory when the script runs is the directory containing the .btdfproj.

Tom
Jun 20, 2013 at 6:44 AM
Hi Tom

Greetings. i tried giving like this.

<Exec Command="sqlcmd.exe -S ZEW-V-0AC2807A\ISDEV -i ..\DBScripts\SQLQuery.sql" />

Its not working as it is searching for the .sql file in the location where SQLCMD.exe is located. Any work around?

Thanks in Advance.
Coordinator
Jun 21, 2013 at 5:04 AM
Edited Jun 21, 2013 at 5:05 AM
Why do you think it is searching for the file in the SQL Server bin folder where sqlcmd.exe resides?

Is your folder structure:
\My.sln
   \Deployment
      My.btdfproj
   \DBScripts
      SQLQuery.sql
If so, when you use AdditionalFiles to package the file into the MSI, its location will move UP one level from DBScripts. If you want to preserve the same structure with a DBScripts subfolder, then you need to copy the file to the redist (obj\Debug\redist) folder yourself and remove the file(s) from AdditionalFiles. Then your command line above with ..\DBScripts should point to the correct location after installing the MSI.
  <Target Name="CustomRedist">
    <MakeDir Directories="$(RedistDir)\DBScripts" />

    <!-- Force MSBuild to expand the item spec into physical file specs -->
    <CreateItem Include="..\DBScripts\**\*.*">
      <Output TaskParameter="Include" ItemName="SQLFilesSourceGroup" />
    </CreateItem>

    <Copy DestinationFolder="$(RedistDir)\DBScripts\%(RecursiveDir)" SourceFiles="@(SQLFilesSourceGroup)"/>
  </Target>
Tom