Archive

Archive for July, 2013

SSIS 2012 Project Deployment – Set CheckpointFile with Parameters

July 23rd, 2013 No comments

The Problem

SSIS in SQL Server 2012 now supports a “Project Deployment” model. Adding things like Connection Strings is relatively intuitive.
I want to use a parameter to set my CheckPointFileName property.
How can you do this so it can be configured by the environment.

The Solution

  1. Create a parameter in your package and call it “CheckpointFileName”
  2. Click On the ellipses “…” on the Expressions property.
  3. In property Choose CheckPointFileName from the drop down
  4. Set the expression by opening the expression builder and choosing the parameter you just created, (sometimes you need to drag and drop the paramater into the expression box)

    ExpressionBuilderCheckpoint

  5. Click OK

Now you can deploy your package and set this parameter as part of you environment setup and change the checkpoint file name as required

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, SSIS Tags:

Post Deployment Script, 72006: Fatal scripting error: Incorrect syntax was encountered while parsing

July 12th, 2013 No comments

The Problem

You create a post deployment script, with a statement like;

:r .\Static Data\Insert Seed Values.sql

The system throws an error and will not build:

72006: Fatal scripting error: Incorrect syntax was encountered while parsing ‘Data\Insert’
[/chsharp]

The solution

There are two problems:

  1. The post deployment script does not like spaces in the file paths, so quote the file path in “double quotes”.
    :r ".\Static Data\Insert Seed Values.sql"
    
  2. The post deployment script runs in SQL CMD mode, so whilst ever you have the script file open it will throw syntax errors either
    1. Close the post deployment script; or
    2. Turn on SQL CMD mode on the file, (in 2012 with latest SSDT this is the last icon in the toolbar

      SQLToolbar
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server, SSDT, Visual Studio Tags: