?
Solved

Sql Server 2008 SSIS, disable pre-execute validation

Posted on 2009-04-17
5
Medium Priority
?
4,771 Views
Last Modified: 2013-11-10
I have an SSIS package that utilizes a "Script Task" on the Control Flow tab as the first step in the process. The package has a series of variables, but here are the ones pertinent to the question:

workingDistrict
sourceDB

workingDistrict is a variable that is set via the "Package Configuration" functionality.

The DataFlow tab has a series of "Flat File -> transformation -> OLE Destination" entries. The database connection utilizes the "sourceDB" variable in its connection string. The "Script Task" is a C# script that queries a database for configuration information (sourceDB, FTP folder names, etc) using the "workingDistrict" number in its query. It then assigns the query values to the package variables (such as sourceDB).

The problem is.... When the package is executed, it tries to validate the database connections BEFORE the script task is run. This obviously creates fatal errors because the connection string is incomplete at that time. If I manually enter data into the "sourceDB" package variable it will validate (which then the script task changes the variable values before the actual work begins).

Is there a way to tell SSIS to NOT validate the connections before runtime, or a way to turn off connection validation for a single entry in my Connections window?
0
Comment
Question by:MMDeveloper
  • 3
  • 2
5 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24167259
You can delay validation.  See this link.

http://wiki.sqlis.com/default.aspx/SQLISWiki/DelayValidation.html

Greg


0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 24170679
I tried that but the error still happens. Looking deeper into the error message, it's the OLE DB Destination parts in the Data Flow portion of the package because it complains that the tables dont exist. I thought that your suggestion (after reading up on it) would prevent even the OLE DB Destination validations to hold off but (unless they have their own personal "delay validation" setting) it doesnt :(

Anything you can think for for Data Flow database references?
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 24171459
OLE DB components have a property called ValidateExternalMetadata which is set to TRUE by default.  Try changing that to FALSE.

Greg


0
 
LVL 15

Author Comment

by:MMDeveloper
ID: 24171891
ok I'll do that when I get to work monday, I appreciate your suggestions
0
 
LVL 15

Author Closing Comment

by:MMDeveloper
ID: 31571418
beautiful, straight, concise answer, to the point, and 100% correct, thank you!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question