SSIS tips: run packages on BIDS ok, but failed on SQL Jobs

 

This is a rather tricky problem.

1. I’ve opened a package via BIDS on production SSIS server, run it over there and data did get populated

2. I tried to run it through a SQL job but it failed with error message meta needs to be examined. 

I’ve checked it thoroughly and found out the column I’ve added in the dev env is not the same on production.

On dev. it’s thirdpartyAppID while on production it’s thirdpartyappid. The difference is on the capital a.

I think if pkg runs on BIDS, this type of difference was ignored(?) while on sql server agent, it gets picked up.

Below is the explanation from the forum

http://social.msdn.microsoft.com/Forums/en-US/22fb4b15-2b2b-4408-96a8-bdc8897c17cf/failed-validation-and-returned-validation-status-vsneedsnewmetadata?forum=sqlintegrationservices

I don't think this a bug, because (I know) SSIS is a case-sensitive tool.

I can see how this could be very frustrating when you are using case insensitive SQL Servers, but there are plenty of areas where case sensitivity is an issue across application boundaries, one of the problems that keeps us all employed!

This does of course explain the metadata error. Can you get the column names in sync?

SSIS expression reference

 

From link http://bisherryli.com/2011/04/18/ssis-96-everybody-needs-integration-services-expression-reference/

 

In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.

In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.

If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.

OK, here is the official link to the Integration Services Expression Reference on MSDN.

So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.

Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:

Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.

Did you notice that it  says expressions are complex frequently?

There are 4 topics on the MSDN reference site.

Topic

Description

Integration Services Expression Concepts

Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.

Operators (SSIS Expression)

Describes the operators that the expression evaluator provides.

String Functions and Other Functions (SSIS Expression)

Describes the functions that the expression evaluator provides.

Advanced Integration Services Expressions

Provides expressions that use multiple operators and functions.

The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.

So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.

Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.

Here is a “simple” expression ( simple only after I remembered AND needed to be &&):

@varHolidayInd == "N" && @varWeekendInd == "N"

About these ads