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.

Package without having to install crystal Report Environment

I 've finished developing system...... I want to install system to client computer without having to install crystal report environament..... Please helpYou need to do a search on the dlls that you need to include with your package.

I use CR 8.5. My version came with a file called runtime.hlp that has a list of everything I need to include depending on what features I used in my program.

Do a search on Crystal's website or on a search engine like www.google.com.

Crystal's website:
http://support.businessobjects.com/search/advsearch.asp

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 trasfer to remote server

I have developed my ssis packages on my local system.Now i want to move them to a remote server.I have connected to the remote server over vpn.Now how do i transfer them to the server.Once i transfer i need to make few changes to the connection.What is the best way to transfer and make it work.

And then i want to schedule the packages to run automatically.

Please let me know

My first suggestion would be to try to read some of the books on-line (BOL). However, barring that, you can transfer the packages in one of a few different ways depending on how it is that you would like to store them on the remote server.

1.

a) You can simply copy the package .dtsx files to a folder on the remote server (or any remote folder that the user account running the package has access to, more on that later).

b) If you are storing it on the msdb you would connect to your ssis server (follow the following link for more information on setting up the dcom correctly to allow you to connect http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx) and then click through the stored packages -> msdb and create a new folder, then right click and press import package. The rest should be pretty self explanatory.

2. What type of changes do you need to make? If they are simply connection string changes and / or variable changes you can use an xml config file, the /set values tab of the job step maintenance screen or the data connections tab (for changes to the connections). I would suggest the xml config file option. If you need to open the package and edit, you can easily do this (provided you set the correct package level protection, i.e. encrypt w/ password or do not encrypt sensitive, etc) by simply opening the package from the remote server.

3. See the following threads / technet articles on setting up a job / credentials / proxy etc.

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

http://support.microsoft.com/?kbid=918760

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

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

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

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

Let us know if you have any more questions or need any more help

Package MajorVersion and MinorVersion, when do they change?

HI, I am wondering when those system variables change. I just cannot see when these are updated. Also, VersionComment, whta is the purpose of this?

Thank you,

Ccote

You change them. For your own version control system.|||

Thanks, is there a way to change those properties via VSS? Or maybe I do not unerstand.

Ccote

|||

No, you would update the values on the properties through BIDS. It is so you can set your own version numbers.

|||

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

|||

ccote wrote:

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

"Major" version might be a complete overhaul of logic. Perhaps you've restructured the way totals are calculated and are now performing via new business rules.

"Minor" version might be a minor change -- a where clause addition, fix a typo, etc...

In the end, it's up to you.|||

ccote wrote:

OK I saw it, thanks! What would be the difference (or usage of) between Major and Minor version? And also, is there a way to set these properties via a script task? Open all packages and set properties?

Thanks again!

Ccote

You can use the SSIS API to load packages and set the properties, then save the packages again, if you want to automate the process.

|||

Ok thanks guys!

package index server

I keep getting a message when I try to do a system update for my lenovo computer, which prevents me for using this utility. "the package index server is temporarily unavailabe, please try again later". Surprisingly, Lenovo could not provide me with any assistance in this matter.Confirm the SQL Server configuration and system too, i don't think this is related to SQL SErver.

Package deployment question

Hi,

I have a standalone SSIS package that I wish to deploy from the file system, as opposed to SQL Server deployment.

Anyways, I was wondering if there's a command line utility for running SSIS packages on an ad hoc basis? What I was thinking was that I would put the call to the command line in a stored procedure using xp_cmdshell, so that the package can be called that way.

Am I thinking about this correctly?

Thanks

dtexec.exe is what you'll need to run from the command line.|||

Thanks. Is this what people generally use for file system deployment?

Question:

Do I need to do anything special with the package first? Or just call it with dtexec.exe from wherever the pkg resides on the file system?

|||

I guess I am wondering about protection levels... and how to work with protection levels when using file system deployment?

I find this all very confusing!

Are there any good articles that discuss all the in's and out's of proper pkg deployment?

|||

Yes, dtexec is what is used to execute from the file system (and from the database). DTEXEC is the runtime engine for SSIS.

If you are running from the file system, I'd recommend setting the ProtectionLevel to SaveSensitiveWithPassword, and use the Decrypt switch to pass it to the package.

|||

Ok thanks.

Q. Concerning "sensitive" data, I'm not 100% sure what constitutes sensitive data? FTP conn mgr passwords and OLE DB conn mgrs using SQL Server logins where you must specify the passwords?

Q. Another question: What if the OLE DB conn mgrs use Windows Authentication to connect to the db and someone else logs on to the server to run the package (and they don't have write permissions to the database)? Will this cause the conn mgr not to be able to connect t the db? In which case, even having a password won't help, right?

Q. But it seems that file system deployment forces the issue, so that I MUST set up a password if I am not deploying to SQL Server, correct?

Thanks much

|||

Hi,

I set the encryption to "EncryptSensitiveWithPassword", and attempted to run this from Management Studio:

EXEC master..xp_cmdshell 'DTExec /FILE "D:\myPackage.dtsx" /DECRYPT "password"'

It appears to run, but these are some of the errors I get. It looks like both it doesn't like the key, and also that SSIS isn't properly installed on this server. Two separate issues, but how can I resolve the key problem first?

Thanks

output

NULL

(1 row(s) affected)

output

Microsoft (R) SQL Server Execute Package Utility

Version 9.00.1399.06 for 32-bit

Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

NULL

Started: 7:56:53 PM

Error: 2007-06-18 19:56:53.89

Code: 0xC0016016

Source:

Description: Failed to decrypt protected XML node "DTSassword" 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-06-18 19:56:54.56

Code: 0xC0202009

Source: myPackage Connection manager "myServer.myDB.mySQLServerLogin"

Description: 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 'mySQLServerLogin'.".

End Error

Error: 2007-06-18 19:56:54.58

Code: 0xC0012024

Source: Script Task

Description: The task "Script Task" cannot run on this edition of Integration Services. It requires a higher level edition.

End Error

Warning: 2007-06-18 19:56:54.58

Code: 0x80019002

Source: OnError

Description: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCoun

t or fix the errors.

End Warning

Error: 2007-06-18 19:56:54.58

Code: 0xC00291EC

|||It seems as though the passwords do not match. CaSE matters. And ensure they match. You did specify a password in the package properties box, right?|||You also have a permission issue to connect to the SQL Server DB. Make sure that whatever user is running the package has rights to connect to the SQL Server|||

Yes, the password is set on the package. It's just "password", so it's no brainer.

So, Rafael, you're saying that the /Decrypt switch is not related to the rights to connect to the server?

I thought that having a password on the package would take care of all the access issues. That is, all the connections are already set in the package, so whoever runs it doesn't need to worry about the underlying connections?

So, how do I resolve this?

Basically, I am using a SQL Server user with read/write permissions in the database for all my OLE DB conn mgrs. Why does this connection fail when I use DTExec? What piece am I missing here?

Need help! Thanks.

|||Have you just tried running this on your own with dtexec on the command line? Without using xp_cmdshell?|||

I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".

It looks like the login credential to connect to that SQL Server are not right.

|||

Rafael Salas wrote:

I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".

It looks like the login credential to connect to that SQL Server are not right.

But that would be because the sensitive information couldn't be decrypted, no?|||Sadie,
Try using EncryptAllWithPassword instead.|||

The package is set up with OLE DB conn mgrs that use SQL Server users.

When I run DTExec.exe, I am doing so as my Windows Login, which has admin rights on the server. I've never had permissions problems with the database.

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

Package configuration using Environment variable

I am doing SSIS package configuration using environment variable.

I have created a system environment variable that points to the dtsConfig file.

I opened the package and choosen the configuration type as environment variable and specified the environment variable

When I click the next button , it doesn't allow me to choose the configurable property.

Please suggest

If you want to have the location of the configuration file stored in an environment variable, but use the config file to actually store settings, you need to set the configuration type to XML configuration file, and set the "configuration location is stored in an environment variable" option. That's an indirect configuration.

owner of a temp table

is there a way to query the system table in SQL Server to determine the
owner of a temp table? I doubt this is possible because it seems as
though everyone is aliased as db_owner.<dlukac@.gmail.com> wrote in message
news:1112724040.143022.24070@.z14g2000cwz.googlegro ups.com...
> is there a way to query the system table in SQL Server to determine the
> owner of a temp table? I doubt this is possible because it seems as
> though everyone is aliased as db_owner.

I'm not sure what you mean - a temp table is unique in the session that
created it, regardless of owner, login or user name (unless it's a global
temp table, of course). Perhaps you can explain what problem you're trying
to solve, or what your aim is?

Simon

Overriding System Security

Hey folks!
First, if I am understanding correctly, even though I remove the
BUILTIN\Administrators from any roles and/or access in RS, they still have full
access to Report Manager, system settings and all. Is this correct?
If the above is true, has anyone found any way to circumvent this. I think this
a stupid concept. Just because a user is in the local Administrators group on a
server, doesn't mean he/she knows how to manage RS.
Thanks in advance.A local administrator can do just about anything on the box, including
format the hard drive. This does not mean the user should do it or knows
that he should not do it. That is why everyone is not an administrator.
Same applies to RS. Does the user need to be an administrator on the box?
If you remove the Builtin Administrators, they will not have full access to
RS. They can still do admin type work on RS, but will find they cannot view
reports and many other things.
--
| Date: Tue, 28 Jun 2005 10:01:41 -0500
| From: Darrell <Darrell.Wright.nospam@.okc.gov>
| User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Overriding System Security
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hey folks!
|
| First, if I am understanding correctly, even though I remove the
| BUILTIN\Administrators from any roles and/or access in RS, they still
have full
| access to Report Manager, system settings and all. Is this correct?
|
| If the above is true, has anyone found any way to circumvent this. I
think this
| a stupid concept. Just because a user is in the local Administrators
group on a
| server, doesn't mean he/she knows how to manage RS.
|
| Thanks in advance.
||||Brad Syputa - MS wrote:
> A local administrator can do just about anything on the box, including
> format the hard drive. This does not mean the user should do it or knows
> that he should not do it. That is why everyone is not an administrator.
>
Good point.
> Same applies to RS. Does the user need to be an administrator on the box?
>
There are a number of AD groups that these users are in that have admin rights
on the server so they can perform various maintenance tasks.
> If you remove the Builtin Administrators, they will not have full access to
> RS. They can still do admin type work on RS, but will find they cannot view
> reports and many other things.
As I said, the BUILTIN\Administrators was removed from RS altogether. Is there
any way, perhaps through IIS, that the local admins could have their access to
RS restricted?
> --
> | Date: Tue, 28 Jun 2005 10:01:41 -0500
> | From: Darrell <Darrell.Wright.nospam@.okc.gov>
> | User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
> | X-Accept-Language: en-us, en
> | MIME-Version: 1.0
> | Subject: Overriding System Security
> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> | Content-Transfer-Encoding: 7bit
> | Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
> | Lines: 1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hey folks!
> |
> | First, if I am understanding correctly, even though I remove the
> | BUILTIN\Administrators from any roles and/or access in RS, they still
> have full
> | access to Report Manager, system settings and all. Is this correct?
> |
> | If the above is true, has anyone found any way to circumvent this. I
> think this
> | a stupid concept. Just because a user is in the local Administrators
> group on a
> | server, doesn't mean he/she knows how to manage RS.
> |
> | Thanks in advance.
> |
>

Override SQL character

I'm trying to search for all records that contain a quotation character in the database. These records were migrated from a mainframe system.

I use the following command:
%'%

The results are:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ' order by cliLastName, cliFirstName, cliBirthName'.

/ladds/lib/getrecordset.asp, line 6

Is there a way to override the quotation character temporarily?

Thanks ...You just have to escape the character. Use this:

like '%''%'

That is, two single quotes, and not a double quote.|||Thanks ... worked like a charm!