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.

PackageID and GUIDs in ExecuteSQL task

Am I looking at a potential bug here or do I not understand the feature properly? I have an ExecuteSQL task that inserts into a table for logging and includes the System::PackageID as one of the values. It's stored in my table as a uniqueidentifier. When I set the output variable in Parameter Mappings tab of the Execute SQL task to VarChar, all works great. WHen I set it to GUID as the data type in that tab, it outputs a different GUID than the actual System::PackageID variable.

-- Brian

This is not a bug, although several have thought it was. If you look at the system variable you will notice that even though it looks like a GUID the type of the variable is a string. This is why when you set the SQL task to use a string type it shows the correct value. However, when you set to GUID you don't get the value you expect because the SQL Task doesn't perform conversion for you so the value being placed in your uid field is actually a guid representation of the pointer not that GUID that is pointed to. Currently, if you want to set the GUID into a uid field in SQL I believe you need to use a script task because then you can get the conversion you expect.

HTH,

Matt

PackageForTheWEB error

I'm having a package for the WEB error when installing
MSSQL 2000 in a clustered windows 2000 advance server.
I have observe the task manager what process was invoke
when the error appeared and it is MSSEARCH.EXE.
What can I do to complete my installation?
Thanks
Janz,
More info is necessary in order to troubleshoot this issue... Could you post
the full output of -- SELECT @.@.version -- Also, attach the file
SearchSetup.log that is usually located at C:\WINNT\ and as well as
sqlstp.log usually located at C:\WINNT\Temp\ and post any errors or issues
related to MSSearch in either of these logs. You can also attach these
files to your post and I'll review them as well. Is your clustered
environment an active/active or passive/active? Which node is this error
occurring on? If you fallover, does the error re-occur?
Note, you should also review your server's Application event log for any
"Microsoft Search" or MssCi source events, errors or warnings as this too
would be helpful info.
Regards,
John
"Janz" <anonymous@.discussions.microsoft.com> wrote in message
news:0ff101c48bd8$577838f0$a601280a@.phx.gbl...
> I'm having a package for the WEB error when installing
> MSSQL 2000 in a clustered windows 2000 advance server.
> I have observe the task manager what process was invoke
> when the error appeared and it is MSSEARCH.EXE.
> What can I do to complete my installation?
>
> Thanks

Package works in BIDS, but not in SQL Server 2005

Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?

Code Snippet

Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()

fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)

If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

Can you be more specific what you mean by "run in SQL Server"? How are these packages being executed?

What other types of activities are failing in your packages? Is it always related to external code?

My thinking based on what you've given so far is that you're trying to load and execute packages from inside a CLR stored procedure. When you do that, you're using SQL's hosted CLR and are subject to SQL's security constraints, such as the Host Protection Attributes.

SQL CLR will deny execution of framework code that has these attributes.
ExternalProcessMgmt ExternalThreading MayLeakOnAbort SecurityInfrastructure SelfAffectingProcessMgmnt SelfAffectingThreading SharedState Synchronization UI |||Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code. Other activities that are failing also include decrypting files, but again, these are run in scripts that use external code.
|||

Dan Wheeler wrote:

Primarily, I'm trying to execute this in a SQL job. However, if I execute it from Integration Services, the result is the same. My errors seem to be related to the external code, but I have 15 packages that all use some external code.

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job.

The difference between executing it from BIDS and from a job is usually just the security context. Under BIDS, it inherits your permissions and under SQL Agent, it is whatever account the agent runs under, which is rarely the same. Anything going on in that code that might be affected by those different permissions? Accessing remote or secured drives or files, perhaps?

There is also another affect of the different user context that I don't see any evidence of here, but gets people all the time. That is that by default, SSIS encrypts sensitive data using a user key. When the package is executed by another user (i.e. SQL Agent), this sensitive data (passwords, mostly) are not available. See threads and documentation for "ProtectionLevel" for more information if you think that might be a factor.

|||

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.
|||

Dan Wheeler wrote:

JayH wrote:

"if I execute it from Integration Services, the result is the same". I don't know if that means BIDS or something else, and if "the same" means success or failure. I'm mostly going off your subject that the package works in BIDS, but not from a SQL Agent job."

It means that they both fail to execute the package.

The only thing remote in this situation are the databases. This package is not failing with database connectivity issues. My security context is a limited user. What permissions would SQL Agent need?

The packages are password encrypted. I provide the password when creating the SQL job. When I execute the package in Integration Services, I provide the password. Therefore, I don't think it is a protection level issue.

I'm still confused about whether the packages run in BIDS or not. If you can call your external code from a simple test harness, then it should work from BIDS.

The error indicates that either GetProviderEnrollmentGenerator is returning Nothing, or that the error is being generated from inside GenerateFile. Is it possible that the variables you're passing into either one of those are not what you think they are? Have you put MessageBoxes in the script to display those values? Being totally agnostic about your code, I'd suggest writing those variables out to a file or something where you can get them verbatim and then calling your external code from a test harness with those values.

|||As the subject states, these packages all execute without error in BIDS. In the first post, I note that I can run this exact code from a console app. I've already created a test harness for this purpose. Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?|||

Dan Wheeler wrote:

As the subject states, these packages all execute without error in BIDS.
Quote "Dan Wheeler"
This code will even run from a command line console without error.

(as you can see, I've already created a test harness for this purpose) Since I'm trying to execute this in Integration Services, will the MsgBox's still show up?

Sorry, I think I'm confused about your use of "Integration Services". I couldn't tell if you meant BIDS or not. I also don't recall seeing that about your console app in the original post.

No, MessageBoxes will not show up if you execute from dtexec (i.e. the agent job). I was thinking about BIDS at that point.
|||Anyone else have anything to offer? I've created a deployment package and still no success.
|||

What if you try running a batch file from the agent job?

My experience mirrors Jay's on this: I think the error is security related. Does the the account that SQL Server or the agent is running under have access to all the files and folders used - not just by the package, but also by the .NET component?

sql

Package with File System Task doesn't work without sensitive data with user keys

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.Could the security permissions be different on your packages that are failing to be moved from those that are succeeding?|||No, they're all set to EncryptSensitiveWithUserKey.

However, all of the packages involved have their sensitive information set to obtaining them from Indirect Configuration Files. The only way around that I found so far was to put in a dummy connection object, set up an XML file package configuration for that dummy, and deploy it that way.

I'm suspecting that the user key isn't generated at all when all the packages get their configs from Indirect Configuration Files. For some reason, DontSaveSensitive doesn't work either. My original package insists on the presence of a key. Might have something to do with file read/write/modify permissions that come with the key?

I still want to do away with the dummy though.

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 Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATETABLE [dbo].[ZipToState](

[ZipCode] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[StateAbbr] [varchar](2)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[City] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[PreferredCityName] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[ID] [int] IDENTITY(1,1)NOTNULL

)ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Package Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATE TABLE [dbo].[ZipToState](

[ZipCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StateAbbr] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PreferredCityName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Package scheduling and error trace-

I am scheduling the package to run on the nightly basis everyday using windows scheduled task.
How do i check whether the package ran successfully or it has given any error.

Can anyone please suggest me if I can log the error in some table of SQL server.

PLease suggest the steps.

When building a package in BIDS, have a look at the logging options. See the SSIS menu item, Logging.

You can log to several locations including a table.

I would expect DTEXEC to send the appropriate return code when it failed, so the scheduled job should fail. Unfortunately the windows scheduler is not very advanced, so you cannot capture output or configure the return code. The lack of output and monitoring means I would not use it.

Package fails but single Task ends with success

Hi

I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.

The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.

Any help welcome.

What is the error you are receiving?

Rafael Salas

|||I had a similar probem the other day.

all the individual steps would run ok in visual studio, but the package as a whole would fail.

the problem was a spurious executable that was not visible on the control flow design screen, but was listed as an executable in package explorer.

its was this spurious executable that was failing, and so causing my package to fail.

have a look to see what is shown as an executable in the package explorer tab?|||

Hi Rafael

Thanks for your answer.

I get the messagebox-error with "Unable to load DLL, Fatal Error!" in the messagebox title and the dll's path in the message body. The dll that is indicated there belongs to the system that should be accessed.

Christian

|||

I've checked the package in package explorer but there's only the one executable that I've just created.

Christian

sql

PACKAGE ENCODING

Hi, a new (little..) problem with xml source.

I have to import large xml file in sql server and I use data transfer task and xml source.

The xml file are generated without specifing any encoding, and so I obtain many character error if i don't change the encoding.

When i put "by hand" (with a xml editor) in the xml file this encoding <?xml version="1.0" encoding="ISO8859-1"?>, the SSIS task works perfectly without any error.

So, i'm looking for a way to use this encoding without editing xml file (more than 500 mb...). the way that i can imagine are:

1) change the package encoding (but I haven't find this kind of settings)

2) change the xml source encoding (but I haven't find this kind of settings)

3) change the console chcp (normally i have 850, i have tested 1252 but without any success)

4) make a xml trasformation (but i don't know the best way); I've tried with XML task without any success...

could anybody help me?

thank you in advance

alessandro

Hi, an update.

I've found a little "workaround" using XML task - patch. In this way I can patch the original xml file with a diff xml file that contain only the encoding settings. The ouput is a sort of "merge" of the xml file and the new enconding.

the real problem is that this package fail because it parse the xml file before create the new one...and it find the illegal character (that are the reasons for wich i want to change the xml encoding...!!!).

I've seen that it works making a test (replacing the illegal caracther "by hand").

So i have founded another way to obtain the same problem....

Please..:! someone help me!

thank you in advance

Alessandro

|||

....Nobody help me?

Now i'm working on .net xml source with scripting...

lavez

Package attempts to query source db constantly when editing

Trying to set up a tranform task between a mysql db using and ADO.NET connection and sql server.
My query to pull from the mysql db is something like "select x,y,z from table where last_updated" > @.User::LastUpdated. This command is set up as an expression for the Data Flow Task and is the value for the [DataReader Source].[SqlCommand]

I have two questions.

Why does the package attempt a query against the mysql database all the time?
And Why is the query attempting to pull the entire table instead of having any regards for my where clause?

I've even added where last_updated > greatest('2006-08-15', '" + @.User::LastUpdated to attempt to get it a where clause even when the parameter isn't set yet.

What is the trick? This is not feasible when pulling from multi-million row tables.

lorijean44 wrote:

Why does the package attempt a query against the mysql database all the time?

Can you qualify what you mean by "all the time"? Do you mean at execution time or design time? What is the package doing when it issues these queries?

lorijean44 wrote:

And Why is the query attempting to pull the entire table instead of having any regards for my where clause?

How have you determined that it is pulling from the entire table?

-Jamie

|||Thank you for your response.
"All the time" means while in design mode, if I click on the Data Reader Source to edit it, when I go to column mappings, when I close the data reader source gui, when I attempt to go back to the control flow window.

I determined it was pulling from the entire table by looking at "show processlist" on the mysql db. I did discover that I needed to do "show full processlist", so that I could see the where clause. But once I figured that out, I could see that it was pulling "select x,y,z from table where last_seen > '1900-01-01'". 1900-01-01 is our default value anytime we are using datetime.

I ended up cheating the system by having it do "select x,y,z from table where last_seen > greatest('2006-08-15', @.last_updated)", so that when it ran the query it would look like "select x,y,z from table where last_seen > greatest('2006-08-15', '1900-01-01')", and not pull so much.
I guess I could set the default to be something more recent. I guess I'm just trying to figure out why does it run the query so often.

Thanks for your time.
-Lori