Packege success, despite task failure

Hello,

When I run my package, a task will fail, however, the package will claim that it was successful. Why is this, and how can I trigger a failed package when one task fails?

Thanks in advance.

Look at your package execution properties. You will see the "Fail package on Failure" option. The package claiming that it is successful is odd. You might want to also look at the "Maximum Error Count" property (again at the package level) to make sure you haven't set it higher than 1. Look at the "Force Execution Result" property also to make sure you don't have it set to "successful".

Hope this helps.

|||

Where do I find 'Maximum Error Count' and 'Force Execution Result'?

|||They are properties of the package (and of the control flow tasks and containers). If you click in an empty area on the control flow of the package, and hit F4, you should see the properties window.

Packege success, despite task failure

Hello,

When I run my package, a task will fail, however, the package will claim that it was successful. Why is this, and how can I trigger a failed package when one task fails?

Thanks in advance.

Look at your package execution properties. You will see the "Fail package on Failure" option. The package claiming that it is successful is odd. You might want to also look at the "Maximum Error Count" property (again at the package level) to make sure you haven't set it higher than 1. Look at the "Force Execution Result" property also to make sure you don't have it set to "successful".

Hope this helps.

|||

Where do I find 'Maximum Error Count' and 'Force Execution Result'?

|||They are properties of the package (and of the control flow tasks and containers). If you click in an empty area on the control flow of the package, and hit F4, you should see the properties window.

package variables passed to Execute SQL Task

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.

Verified I'm using an OLE DB valid connection.

Verified the variables are properly mapped in the parameters mapping section of the task properties.

Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

Changed the Bypassprepare property to 'True'.

Breakpoints and watch windows to verify the variable values.

I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

Also, the code for the first package that holds the parameter is:

DECLARE @.DatabaseName varchar(100)
SET @.DatabaseName = ?
DECLARE @.SPID smallint

And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

The Parameter Mapping has the following values:

User:atabasename Input Varchar 0

And the Variables set up has the following values:

databasename package string DB_NAME

Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

Thanks!|||

You could use an alternative approach to built the SQL statement. Create a new variable, let's say SQLStatement; then use an expression (set EvaluateAsExpresion=True) in that variable to form the SQL statement. something like:

"Select * from " + @.[User::Variable1]

Then in the execute SQL task set SQLSourceType=Variable and select the variable name in SourceVariable drop-down list.

I like this approach because you an see easily how the sql statement is being built.

|||Thanks for the quick reply Rafael. I am unclear as to how this would resolve the issue. The package contains three lengthy Execute SQL tasks, and the package needs to be replicated for several different databases that are independently accessed in a development environment. My original idea was to use the package variable to simply change the DB name. Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task? If so, it's definitely worth a try, but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Thanks again!
|||

jheywood wrote:

Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task?
Thanks again!

Yes, that is my idea. That give you the benefit of testing the expression and check how exactly the sql statement is gong to be sent.

jheywood wrote:

but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Not sure..did you try changing the value of ByPassPrepare in the execute sql task?

|||Yes, I tried that too. I was hoping it was maybe just some stupid syntax problem that I might have overlooked with the parameter or the variable, but I guess not.

I'll try the other method since that definitely seems like a viable option. Thanks so much for the help!

Please let me know if you think of anything else about the var use, or need more information. I'm really stumped on it.
sql

Package Fail connections on moving to server

I am working with the ssis packages.I am working in the development enviroment.Once i place them on the server all my connection to text files fails.I dont want to sit and Keep changing with respect to server again and again.

What is the best way to set connection like for example:in my development it can be on e:\data folder

But on server F:\Data

With me changing again and again, can this be set within the package in the development environment.

Please le me know.

Use a package configuration to populate a variable with the location of the files. Then use an expression on the connection manager to combine the file location variable with the hard-coded filenames.
|||

Would you please explain in little more details

Thanks in advance

|||If you aren't familiar with Package Configurations, you should read the topic in Books Online. You can use them to set the connection string on your flat file connection managers, as Jay suggests.