SSDT – Error SQL70001 This statement is not recognized in this context

One of the most common errors I get asked about when using SQL Server Data Tools (SSDT) Database Projects is the error “This statement is not recognized in this context”. This is actually a pretty simple error to fix.

Envision this scenario. You have a simple table:

CREATE TABLE [dbo].[Test]
( [Id] INT IDENTITY NOT NULL PRIMARY KEY
, [SomeData] NVARCHAR(20) NOT NULL
)

Great. So then you want to have a post deployment script which will populate it with some default value. Because we are following best practices we creating a post deployment script which then calls the script to populate the default data.

:r .\InsertSomeData.sql

Then we have the script InsertSomeData.sql itself:

INSERT INTO [dbo].[Test] ([SomeData])
VALUES (‘Arcane Code’)

After inserting the code, or doing a build, you get this ugly error pop up in the error window:

image

So what happened? Well, when you went to insert the script you had these options in the dialog:

image

 

If you aren’t careful, you could accidentally pick the “Script (Build)” option (highlighted in blue). This option attempts to compile and run the code as DDL (Data Definition Language, the T-SQL syntax which creates tables, indexes, etc.) syntax. Things like Insert statements though are considered DML (Data Manipulation Language) code, and aren’t eligible to be compiled as part of the project. This is what generates the “This statement is not recognized in this context” error. You are essentially putting DML code where only DDL is allowed.

But don’t despair, this is extremely simple to fix. In SSDT, simply bring up the Properties dialog for the SQL script (click in the SQL script, then View, Properties in the menu). Pick the Build Action property, and change it to None.

image

And that’s it, the error “SQL70001 This statement is not recognized in this context” should now vanish from your error list.

Creating a Data Warehouse Date Id in Task Factory Advanced Derived Column Transformation

The company I work for, Pragmatic Works, makes a great tool called Task Factory. It’s a set of transformations that plug into SQL Server Integration Services and provides a wealth of new controls you can use in your packages. One of these is the Advanced Derived Column Transformation. If you are familiar with the regular Derived Column transformation built into SSIS, you know that it can be painful to use if you have to create anything other than a very basic calculation. Every try typing something complex into that single row tiny little box? Egad.

The Task Factory Advanced Derived Column transform allows you to pop up a dialog and have true multi-line editing. In addition there are 180 addition functions to make your life easier. Which is actually the point of this whole post.

As a Business Intelligence developer, one of the things I have to do almost daily is convert a date data type to an integer. Most dates (at least in the US) are in Month / Day / Year format. Overseas the format is usually Day / Month / Year (which to me makes more sense). SQL Server Analysis Services loves integer based field, so a common practice is to store dates as an integer in YYYYMMDD format.

Converting a date to an integer using the derived column transform can be ugly. Here’s an example of a fairly common (although not the only) way to do it:


(DT_I4)((DT_WSTR,4)YEAR(MyDateColumn) + RIGHT("00" + (DT_WSTR,2)MONTH(MyDateColumn),2) + RIGHT("00" + (DT_WSTR,2)DAY(MyDateColumn),2))

Task Factory makes this much easier. There is a ToChar function which converts columns or values to characters. This function allows you to pass in a format to convert to. Wrap all that in a ToInteger function and away you go. Check this out:


ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))

Much, much simpler. One thing, the case of the format is very important. It must be yyyyMMdd, otherwise it won’t work. If you want to extend this more, you can actually check for a null, and if it is null return a –1 (a common Id for a missing row) or another special integer to indicate a missing value, such as 19000101.


IIf(IsNull(MyDateColumn)
   , -1
   , ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))
   )

Here we first check to see if the column is null, if so we return the missing value, else we return the date converted integer. And yes, you can do multi line code inside the Advanced Derived Column Transformation.

As you can see the Advanced Derived Column Transformation makes working with dates much, much easier than the standard derived column transformation. This is such a common need that, at the risk of sounding like an ad, I decided to blog about it so I can share this with all my clients in the future.

(Just to be clear, it’s not an ad, I was not asked to do this, nor did I receive any money for it. Mostly I did this post just so I could share the syntax when I start each project or training class.)