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.

packaging MSDE 2000 with PDW

How can i package MSDE 2000 (not MSDE 7) with my application using package and deployment wizard?
Burlar:
I have been on this list for a year and installing MSDE with an application
has been the number one problem since then.
As far as I know, it cannot be done reliably.
Paul
"Burlar" <bolanoble@.yahoo.com> wrote in message
news:0E323F53-0F9E-47AB-84A5-70705257E508@.microsoft.com...
> How can i package MSDE 2000 (not MSDE 7) with my application using package
and deployment wizard?
|||Paul,
Then how have you been able to solve the problem. I don't want to have to install msde on every target machine. I feel it is bad practice.
|||Burlar:
I haven't. I am rolling out my app. in a month and seriously considering
telling
users that they must be on Win 2K or above as this will solve a lot of
problems
apparently ...
Paul
"Burlar" <anonymous@.discussions.microsoft.com> wrote in message
news:2B577F83-7918-4F82-ACCA-442C7F4F3C42@.microsoft.com...
> Paul,
> Then how have you been able to solve the problem. I don't want to have to
install msde on every target machine. I feel it is bad practice.
|||Hi Burlar,
In addtion to Paul's advice, check out my comments to anaximan (in this
folder) regarding the PDW. It should now not be being used.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Burlar" <bolanoble@.yahoo.com> wrote in message
news:0E323F53-0F9E-47AB-84A5-70705257E508@.microsoft.com...
> How can i package MSDE 2000 (not MSDE 7) with my application using package
and deployment wizard?

PackageStart/End Events

Just finishing of a large ETL system and have aquestion about the following:-

We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?

My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something

In a parent/child package structure I place all my logging in one place - in the parent package. All events (including child package OnPreExecute/OnPostExecute events) "bubble-up" to the parent package where they are "trapped" by the log provider.

-Jamie

|||Thats exactly what I'm doing, I get everything but the start/end events.|||

Do you mean OnPreExecute & OnPostExecute?

I can't imagine why you're not getting them. it works for me!

-Jamie

|||The parent package adds entries to SYSDTSLOG90 for PackageStart and PackageEnd plus any other log entries (Warnings, Errors etc) from either the Parent or the Child, but excluding the PackageStart and PackageEnd entries for the Child package. Its those events I would like to get logged, now we have no warnings ot errors the log looks it bit thin with just Parnet PackageStart and then 4-5 hours later the Parent PackagEnd|||

Then I'm stumped!

The parent logs its 'PackageStart' , it the executes the 'Execute Package' Task a number of times all packages execute normally and bubble up any Errors or Warnings to the parent. The only thing that appears not to bubble up (and consequently not logged to sysdtslog90) are the Child PackageStart and PackageEnd events.

In the parent I have configured the 'Execute Package' Task so: - DisableEventHandlers=False, and EventHandlers for OnError, OnPostExecute, OnPreExecute, OnTaskFailed and OnWarning, each of these has no Tasks but merely serves as a place holder in order to set Propogate=False.

It's not causing any problem to our applicaion other than the fact that I am trying to write a UI that links our application maintained log with sysdtslog90

Any pointers gratefully accepted

Paul

PackageStart/End Events

Just finishing of a large ETL system and have aquestion about the following:-

We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?

My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something

In a parent/child package structure I place all my logging in one place - in the parent package. All events (including child package OnPreExecute/OnPostExecute events) "bubble-up" to the parent package where they are "trapped" by the log provider.

-Jamie

|||Thats exactly what I'm doing, I get everything but the start/end events.|||

Do you mean OnPreExecute & OnPostExecute?

I can't imagine why you're not getting them. it works for me!

-Jamie

|||The parent package adds entries to SYSDTSLOG90 for PackageStart and PackageEnd plus any other log entries (Warnings, Errors etc) from either the Parent or the Child, but excluding the PackageStart and PackageEnd entries for the Child package. Its those events I would like to get logged, now we have no warnings ot errors the log looks it bit thin with just Parnet PackageStart and then 4-5 hours later the Parent PackagEnd|||

Then I'm stumped!

The parent logs its 'PackageStart' , it the executes the 'Execute Package' Task a number of times all packages execute normally and bubble up any Errors or Warnings to the parent. The only thing that appears not to bubble up (and consequently not logged to sysdtslog90) are the Child PackageStart and PackageEnd events.

In the parent I have configured the 'Execute Package' Task so: - DisableEventHandlers=False, and EventHandlers for OnError, OnPostExecute, OnPreExecute, OnTaskFailed and OnWarning, each of these has no Tasks but merely serves as a place holder in order to set Propogate=False.

It's not causing any problem to our applicaion other than the fact that I am trying to write a UI that links our application maintained log with sysdtslog90

Any pointers gratefully accepted

Paul

Packages related with its jobs...

Hi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

RegardsHi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

Regards

Try:

select * from sysdtspackages where id = '[package id]'

[edit: d'oh, I should read SQL BOL first! The /~Z is an indicator that the string that follows is encrypted. I do not know SQL's encrption algorithm so I'm afraid I can't really help here.]|||thanks. I have tested the sysdtspackages table but I cant find any useful information to relate a job with the package that is running.

Meanwhile I will try SQL BOL.

Regards|||Is the dts packagae writing a log file in the logs folder of the mssql folder? If not, modify the dts package to write the log. Then, aftter the dts packagees execute, check the log. It will hold the clear text name of the package and the results of each step in the package. You should be able to correlate the run time and the associated package. And if you have to delete old logs, the dts package will recreate them on the next execution.

Packages not executing right on production server?

Hi,

I have developed about 20 to 30 packages that are executed on a specific order by a parent package that iterates a foreach cycle...

Has in development server all packages run just fine... when deployed to the production server and executed the first child package to run gives error and says that the logging text file was unable to find the file? It was supposed for the log to create and send information to the file right? So i think this is a little bit weird...

Any idea of what is going on here?

Regards,

Does the lggoing file folder exists, and does the execution security context (account) used have permissions to write to that folder? Different machines, so maybe the drives and folders are different, and your may well be using a different security context.|||

Where should i see the security context?

Anyway i have done the directory full previledges to "everyone" and nothing... one of the tasks creates the log just fine... but the other don't... the directory exists and i can't find a reason for it not create the file......

|||

The security context is the user who executes the package. Packages still execute on the machine which hosts the calling process, so were you are running DTEXEC or Visual studio, and they run under the security context of the user running the host program.

For scheduled jobs this is the SQL Server Agent service account or proxy account depending on your setup.

|||

I have already checked and all machines are setup the same...

I'm using visual studio 2005 in the production machine now with the project open and i can't really figure out what this error comes from... It seems that the variables are sent to the child package after the ssis logging so when ssis logging wants to start beeing used it can't...

Imagine i pass a variable called SOLUTION_DIRECTORY and that variable setups many things the log file path is one of them... os if this variable comes after the start of logging then we have problems....

Does this happens? Regards

|||

It depends how you're setting it. If its via a configuration then it should occur before you start logging.

If you pass it via the command-line, I'm not so sure.

Try outputting the the connection string of the log file to a different log provider, perhaps the event viewer. Output the connection string using this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

It really seems that the logging is starting first then the package configuration that receives the variables from the parent package... this is really stupid... damn!

I tried to setup the variable with a default value correct and it worked... so how will i overcome this problem? I really need to log onto files and the file destination is dynamic... :( !!!

|||

I do a very similar thing. I use an XML config to set a variable to contain a path to my rootfolder and then dynamically build the logfile connection string from that. Exactly as you are doing.

This works fine. I'd be very very surprised if parent configurations occur at a different time.

-Jamie

|||

Has for what it seems it really is this way...

How can you explain the fact that if i set the variable in the child package to the right path it works and if not and pass the right path by the parent package it doens't? At least it can be very weird indead...

Another thing... is there anyway to validate if a directory already exists before creating it without using a script?

OUTPUT:

Error: 0xC001404B at CCCTT000D, Log provider "SSIS log provider for Text files": The SSIS logging provider has failed to open the log. Error code: 0x80070003.

The system cannot find the path specified.

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::SOLUTION_DIR".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_SERVER".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_USERID".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_PASSWORD".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "CONF::DESTINATION_DATABASE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "IN::MODULO".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "OUT::COD_EMPRESA".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::PERIODO_CURRENTE".

Information: 0x40016042 at CCCTT000D: The package is attempting to configure from the parent variable "TEMP::STEP".

Information: 0x0 at Script Task: ligacao: CCCTT000D_<EMPRESA> UNL connectionstring: D:\CMSINTRABI\CG\DADOS\200401\CCCTT000D_CMS.UNL

Information: 0x0 at Script Task: ligacao: CONNECTION connectionstring: Data Source=CANARIO;Initial Catalog=CMSINTRACG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Information: 0x0 at Script Task: ligacao: FICHEIRO_LOG connectionstring: D:\CMSINTRABI\CG\LOGS\200401\CCCTT000D_CMS_CT.LOG

Has you can see the log gives error first then the variables are caught... :(

Regards,

|||

Yes its confirmed... it really seems that the logging starts first then the package configuration...

My text file for the ssis logging has this path configured in expressions:

@.[IN::SOLUTION_DIR] + "\\"+ @.[IN::MODULO]+"\\LOGS\\" + (DT_WSTR,6)@.[IN::PERIODO_CURRENTE] + "\\" + @.[System::PackageName] + "_" + @.[IN::COD_EMPRESA] +"_"+ @.[IN::STEP] +".LOG"

Well, all these variables come straight from the parent package.... if i don't set them with a default value the package will blow... but it i set them with an existing path it works just fine...

So this is it... Now WHY the hell don't the package configuration run in first place?

How will i make the logging this way? I really need help here! :(

Regards,

|||Can you not use a single configuration file and set it on all packages. It can supply the values you require to each and every package, rather than using the parent package variables. Configurations do happen before validation and execution. What is the problem with configurations you mention?|||

Well imagine that the parent package runs childs packages and send paremeters to each package...

Imagine for instance i run all packages by year month and i want the child packages to log the file to a directory somewhere in the corresponding year month... Ex: C:\logs\200604

but if that directory doenst exists at first then the error occurs... if the directory already exists then it works just fine :)

Basically i must be shure that the first directory setup must exist...

sql

Packages in SQL Server

Hi: Can any one please give me an example of Package in SQL server which i can test.
Thanks.What sort of package? Maybe you should ask at UPS?|||I am guessing you are coming from Oracle. MSSQL does not have packages like Oracle. However in 2005, there is something sort of similar, which is a CLR assembly. You can implement procs, functions, types, triggers, etc in an assembly and then expose them in sql.

Here are some examples:

http://msdn2.microsoft.com/en-us/library/ms131046.aspx

packages are empty

Hello,
I have this problem on my SQL Server, when I go to local packages and open a
package, nothing shows inside it, when I execute it, it says that completed
successfully but no tasks are shown. I have to restart the whole computer
(Win 2K Server) inorder to see what is inside a package again, then after a
while or the next day, they redisapear.
Any help in solving this would be highly appreciated
thank youHi
Very strange. Have you opened the package on the server or on the client
workstation?
"M K W" <admin@.admin.com> wrote in message
news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> Hello,
> I have this problem on my SQL Server, when I go to local packages and open
a
> package, nothing shows inside it, when I execute it, it says that
completed
> successfully but no tasks are shown. I have to restart the whole computer
> (Win 2K Server) inorder to see what is inside a package again, then after
a
> while or the next day, they redisapear.
> Any help in solving this would be highly appreciated
> thank you
>|||I've heard of this when the dts dlls are not registered
correctly. Try reregistering the dts dlls or reapply Service
Pack 3.
-Sue
On Tue, 24 Aug 2004 09:51:06 +0300, "M K W"
<admin@.admin.com> wrote:
>Hello,
>I have this problem on my SQL Server, when I go to local packages and open a
>package, nothing shows inside it, when I execute it, it says that completed
>successfully but no tasks are shown. I have to restart the whole computer
>(Win 2K Server) inorder to see what is inside a package again, then after a
>while or the next day, they redisapear.
>Any help in solving this would be highly appreciated
>thank you
>|||We have the same issue and if, as the previous poster asked, the
difference is only evident when connecting from a w/station, the
problem is in the build.
Not sure exactly what fixes it, may be re-application of latest
SP/patch.
Regards
ALI
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl>...
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> > Hello,
> > I have this problem on my SQL Server, when I go to local packages and open
> a
> > package, nothing shows inside it, when I execute it, it says that
> completed
> > successfully but no tasks are shown. I have to restart the whole computer
> > (Win 2K Server) inorder to see what is inside a package again, then after
> a
> > while or the next day, they redisapear.
> > Any help in solving this would be highly appreciated
> > thank you
> >
> >|||Hello Uri,
Thanks for your reply, I am opening the package from the server. I havn't
installed the latest SP yet, but I am afraid not to work too.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e0e$9faiEHA.3608@.TK2MSFTNGP09.phx.gbl...
> Hi
> Very strange. Have you opened the package on the server or on the client
> workstation?
>
> "M K W" <admin@.admin.com> wrote in message
> news:ewfg%23XaiEHA.1644@.tk2msftngp13.phx.gbl...
> > Hello,
> > I have this problem on my SQL Server, when I go to local packages and
open
> a
> > package, nothing shows inside it, when I execute it, it says that
> completed
> > successfully but no tasks are shown. I have to restart the whole
computer
> > (Win 2K Server) inorder to see what is inside a package again, then
after
> a
> > while or the next day, they redisapear.
> > Any help in solving this would be highly appreciated
> > thank you
> >
> >
>sql

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.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.sql

Package.Execute/IDTSEvents vs DTExec

I am executing a package via vb.net with package.execute using the IDTSEvents interface inheriting DefaultEvents. I am trying to emulate DTExec functionality. The package runs fine and I am catching events, but I don't get nearly as many info and progress events as are put out by DTExec. From my understanding DTExec also used the managedDTS application object, is that not the case? If it uses this does anyone have an ideas on how it is grabbing more events? For example I don't get these events that DTExec shows ..

Info: 2006-09-26 14:22:27.97
Code: 0x40016041
Source: DWB02130
Description: The package is attempting to configure from the XML file "D:\SSIS\ConfigurationsDatabase.dtsConfig".
End Info
Info: 2006-09-26 14:22:28.05
Code: 0x40016040
Source: DWB02130
Description: The package is attempting to configure from SQL Server using the configuration string ""Configurations Database";"[dbo].[SSISConfigurations]";"DWB02130";".
End Info

I also don't get as many progress updates during validation, I basically get 0 and 100% while DTExec shows several 1%, 3%, 10%, etc.

Any help is greatly appriciated.

Thanks!
Harry

Do you set fireAgain parameter to true for events like OnInformation, OnProgress? If not, the event might not be fired again to avoid overhead associated with sending an event (that presumably nobody listens too).

You can also implement IDTSLogging interface and pass it to Execute method in addition to IDTSEvents interface.

Package.dtsx -> XML format description/manual request

Hi All,

As I see it is possible to view a package (dtsx) in XML form.

Is the XML stable and documented? Would it be good decision to

use the XML as source for third party development or we need to use

some API to get package's content ? Where I could get the package's XML

structure description/manual ?

Thanks,

Sergiy

I'd say the overall schema is stable, but it is not publically documented so subject to change I guess. What will change is the detailed sections within tasks or components that describe properties. Task or component developers can change that whenever they feel like, and may do. Whilst that may sound a bit dangerous, it is perfectly acceptable since they would be expected to write the appropriate code to upgrade between component versions, or even downgrade gracefully if required, it is possible.

The safest way would be to use the API/object model, but XML may be sufficient for what you need. Just think what will happen if a new element or attribute appears because a new version of a task comes out and it has a new property. Examples of this are some of my own developed tasks, or changes MS made in SP1.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

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 wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

Package won''t run from job

I imported my packages to SQL Server 2005.

Under Stored Packages > msdb, if I right click the package name an select "run package", the package runs fine without errors.

However if I put the same package in a job and run the job, the job fails every time and won't tell me why. It just says to check the history. This is what the history says:

Executed as user: myDomain\SQLService. The package execution failed. The step failed.

Seems like some kind of permission failure.

Any ideas, please. This permissions stuff is driving me nuts.

Here are all the permission levels that I am aware of when running a job:

1.) permissions at the package level (conn mgrs, ftp mgrs, etc)

2.) rely on SQL Server storage for roles and access control - what does even mean?

3.) job owner (sa) - how does this differ from the job step owner?

4.) job step > run as [user] (SQL Server Agent Account) - why does this require a separate owner?

5.) package > server > Log on to the server [as user] - what the heck is this?

It's maddening. For example, I have no idea what #5 means - when it (the package) is logging on to the server, WHO is it logging on as if the package is running in a job? The job owner? The job step owner? Or something entirely different?

I've asked other people here if they have any clue how all these permissions interrelate, and frankly no one knows. It's a big mystery..... sort of like the big bang. Don't ask me how I passed my MCDBA. That is another great mystery of the universe.

It's no wonder I can't get this thing to run!

Thank you, error handler, for sending me task-related error messages.

So here's the problem:

The script threw an exception: Could not find a part of the path 'o:\myFolder'.

In one of my packages, I have a script that gets file names from the network drive, o:

My other packages reference the d: drive with no problem. It's the o: drive that's having problems.

So maybe whoever is running the package doesn't have permissions to the o: drive, right?

So, getting back to my first post, who needs the permission to read the o: drive? Probably the step owner?

|||

See this classic KB that explains it:

http://support.microsoft.com/default.aspx/kb/918760

The access to o: should be granted to the user who runs the package - Agent service account or Proxy account if you use it for this job. In your case this is currently myDomain\SQLService (from "Executed as user..." message). Also, if o: is network drive, switch to using UNC path (\\server\path) as drive mapping is user-specific.

|||

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

However, I still have some questions about how all the pkg/job permissions interrelate to each other:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

3.) job owner (sa) - how does this differ from the job step owner?

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

If anyone could answer any or all of these questions, and #5 especially, I would really appreciate it!

Thanks much

|||

sadie519590 wrote:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

sadie519590 wrote:

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

Without more context, I'm not sure. My best guess is that is referring to storing packages in SQL Server rather than the file system. If you do that, it can handle access to the packages.

sadie519590 wrote:

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

Yes, unless you have set up a proxy.

|||

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

That's because the conn mgrs will be using the SQL Agent account, right? (Because the this is job step owner?)

Thanks|||Correct.|||

sadie519590 wrote:

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

o: is a mapped drive, correct? Mapped drives are a user-session concept. That is, when you log on, you can map drives. However, if I remote into that box and log on as well, I don't get to see your mapped drives. Service accounts, such as those that run SQL Server, SQL Server Agent, etc... don't have any clue what a mapped drive is. As soon as you log off of the box, the mapped drives go away. So yes, if you are going to use network shares, you HAVE to use UNC.|||

Yes, the unc path did solve the problem.

Thanks all.

Package won't run from job

I imported my packages to SQL Server 2005.

Under Stored Packages > msdb, if I right click the package name an select "run package", the package runs fine without errors.

However if I put the same package in a job and run the job, the job fails every time and won't tell me why. It just says to check the history. This is what the history says:

Executed as user: myDomain\SQLService. The package execution failed. The step failed.

Seems like some kind of permission failure.

Any ideas, please. This permissions stuff is driving me nuts.

Here are all the permission levels that I am aware of when running a job:

1.) permissions at the package level (conn mgrs, ftp mgrs, etc)

2.) rely on SQL Server storage for roles and access control - what does even mean?

3.) job owner (sa) - how does this differ from the job step owner?

4.) job step > run as [user] (SQL Server Agent Account) - why does this require a separate owner?

5.) package > server > Log on to the server [as user] - what the heck is this?

It's maddening. For example, I have no idea what #5 means - when it (the package) is logging on to the server, WHO is it logging on as if the package is running in a job? The job owner? The job step owner? Or something entirely different?

I've asked other people here if they have any clue how all these permissions interrelate, and frankly no one knows. It's a big mystery..... sort of like the big bang. Don't ask me how I passed my MCDBA. That is another great mystery of the universe.

It's no wonder I can't get this thing to run!

Thank you, error handler, for sending me task-related error messages.

So here's the problem:

The script threw an exception: Could not find a part of the path 'o:\myFolder'.

In one of my packages, I have a script that gets file names from the network drive, o:

My other packages reference the d: drive with no problem. It's the o: drive that's having problems.

So maybe whoever is running the package doesn't have permissions to the o: drive, right?

So, getting back to my first post, who needs the permission to read the o: drive? Probably the step owner?

|||

See this classic KB that explains it:

http://support.microsoft.com/default.aspx/kb/918760

The access to o: should be granted to the user who runs the package - Agent service account or Proxy account if you use it for this job. In your case this is currently myDomain\SQLService (from "Executed as user..." message). Also, if o: is network drive, switch to using UNC path (\\server\path) as drive mapping is user-specific.

|||

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

However, I still have some questions about how all the pkg/job permissions interrelate to each other:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

3.) job owner (sa) - how does this differ from the job step owner?

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

If anyone could answer any or all of these questions, and #5 especially, I would really appreciate it!

Thanks much

|||

sadie519590 wrote:

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

sadie519590 wrote:

2.) rely on SQL Server storage for roles and access control - what does mean? does this relate to #1 above?

Without more context, I'm not sure. My best guess is that is referring to storing packages in SQL Server rather than the file system. If you do that, it can handle access to the packages.

sadie519590 wrote:

5.) New Job Step > General tab > SQL Server Integration Services Package > Server: Log on to the server [Windows Authentication] - will this be the same login as the job step owner, that is, the SQLServer account?

Yes, unless you have set up a proxy.

|||

1.) permissions at the package level (ole db conn mgrs, ftp mgrs, etc) - the SQLService account does not have anything to do with this, right?

It depends - if your connection managers are using Windows Authentication, then the account SQL Agent is running under does have an impact.

That's because the conn mgrs will be using the SQL Agent account, right? (Because the this is job step owner?)

Thanks|||Correct.|||

sadie519590 wrote:

So it seems the problem has to do with the fact that the SQLService domain account does not have permissions to access o:, or that I need to use a UNC path.

o: is a mapped drive, correct? Mapped drives are a user-session concept. That is, when you log on, you can map drives. However, if I remote into that box and log on as well, I don't get to see your mapped drives. Service accounts, such as those that run SQL Server, SQL Server Agent, etc... don't have any clue what a mapped drive is. As soon as you log off of the box, the mapped drives go away. So yes, if you are going to use network shares, you HAVE to use UNC.|||

Yes, the unc path did solve the problem.

Thanks all.

Package won't run correctly when called

I have a stiuation where I need to import data from Excel and of course I'm using DTS to do so. Because the spreadsheets have a variety of datatypes in them, I have to make sure that DTS recognizes certain rows as INT so they will import properly, to do so I add 5 rows of 1's to the top lines of the individual sheets in the correct columns. After the 1's are entered I import to staging tables and then into the production tables. The package itself is run from a job and the job is called by a stored procedure running sp_start_job. Here is a sample of the ActiveX script I am using to alter the spreadsheets:

*****************************************************************
Function Main()

On Error Resume Next

Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet

Dim sFilename
Dim sSheetName

sFilename = "\\Share\LCS Database Information - Data Test.xls"
sSheetName = "Experience Spreadsheet"
Set Excel_Application = CreateObject("Excel.Application")

Excel_Application.DisplayAlerts = False

' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)

Excel_WorkBook.Worksheets(sSheetName).Activate

FOR x = 2 TO 6 STEP 1
FOR i = 1 TO 5 STEP 1
Excel_WorkBook.WorkSheets(sSheetName).Cells(i,x).Value = 1
NEXT
NEXT
*************************************************************************

It does this to 3 sheets and then the import steps run.

Now, when I execute the package in Enterprise Manager it works exactly as I want. However when I run it through the stored procedure, the ActiveX doesn't run properly. My stored proc looks essentiall like this:

****************************************************************************
--Creat TEMP table for the DTS package to store values from the spreadsheet
EXEC pCreateTEMPTables

IF @.@.ERROR <> 0
BEGIN
SET @.Segment = 1
GOTO sqlerror
END

--Run the DTS package that will pull data into temp tables so the individual --data points can be used to search up keys
EXEC msdb.dbo.sp_start_job
@.job_name = 'LCSRunDTS'

EXEC more.procs
****************************************************************************

One thing about the spreadsheets is that they have links in them wanring messages get thrown up during the ActiveX script about updating the links. I tried to turn them off but one message, "One or more links can't be updated" which comes up if you click "Update" on the previous warning or supress warnings, just won't go away.

Any ideas? Thanks
Well, I got the first problem above figured out. When I ran the package directly from Enterprise Manager, the ActiveX object was created on my local system and used Excel there to do what it needed to. When I tried to run the package on the server which didn't have Excel installed, my script failed. My new solution (as soon as it works) is to use WshRemote to run the script to alter the spreadsheets using a computer with Excel installed. The following script works when run from my PC but fails with the message "Error Source = Microsoft VBScript runtime error, Error Description: Permission Denied" when run as part of the DTS package called from a stored procedure via dtsrun:
--
Dim Controller, RemoteScript
Set Controller = CreateObject("WSHController")
Set RemoteScript = Controller.CreateScript("\\Server\Share\ExcelAdd1s.vbs", "MyLocalPC")
RemoteScript.Execute

Being new to the DBA world, I need some help with permissions. Is it that the SQL Server account doesn't have access to the shared drive, or to my PC? Where can I look to see what user is running the DTS package? Are there any questions that I'm not asking that I should be? Any help would be appreciated.|||When yuo run the package from EM directly your logged in account needs to have permission to the share. When it runs from an agent job the SQL Agent account must have permission to do the same thing, it must be to a UNC share and not a mapped drive