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, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

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

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

sql

Packages, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

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

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

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 Yukon ?

Hello,
does anyone know if Yukon will support something like Oracle packages ?Hi,
I wish.. I already filed a sqlwish couple of months ago related to this. Go
ahead and make one..
"eXavier" wrote:

> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>|||Hi
Not in the same way, but SQL Server 2005 supports .NET CLR Objects.
http://www.microsoft.com/sql/2005/p...p30features.asp
Regards
Mike
"eXavier" wrote:

> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>

packages in Yukon ?

Hello,
does anyone know if Yukon will support something like Oracle packages ?Hi,
I wish.. I already filed a sqlwish couple of months ago related to this. Go
ahead and make one..
"eXavier" wrote:
> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>|||Hi
Not in the same way, but SQL Server 2005 supports .NET CLR Objects.
http://www.microsoft.com/sql/2005/productinfo/top30features.asp
Regards
Mike
"eXavier" wrote:
> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>

packages in Yukon ?

Hello,
does anyone know if Yukon will support something like Oracle packages ?
Hi,
I wish.. I already filed a sqlwish couple of months ago related to this. Go
ahead and make one..
"eXavier" wrote:

> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>
|||Hi
Not in the same way, but SQL Server 2005 supports .NET CLR Objects.
http://www.microsoft.com/sql/2005/pr...30features.asp
Regards
Mike
"eXavier" wrote:

> Hello,
> does anyone know if Yukon will support something like Oracle packages ?
>
>

Packages in SQL Server

Hi: Can any one please tell me about Packages and their use in SQL Server?
Thanks,Are you talking about DTS packages? Please expand what you mean by "Packages"

Packages in SQL Server

Hi: Can any one please tell me about Packages and their use in SQL
Server?

Thanks,
AnnaAnna (colleen1980@.gmail.com) writes:

Quote:

Originally Posted by

Hi: Can any one please tell me about Packages and their use in SQL
Server?

Packages are specific to the area of DTS (Data Transformation Services)
in SQL 2000 and SSIS (SQL Server Integration Services) in SQL 2005. SSIS
is the successor to DTS. Both DTS and SSIS permits you receive files and
transform them before you load them into SQL Server. Exactly how, I don't
really know, because I have not used any of them.

Some people, particularly those that maintain a data warehouse which
receives data from all over the place, work a lot with DTS/SSIS, but if
you like me spend your time with an OLTP system, you may never get that
far.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Anna (colleen1980@.gmail.com) writes:

Quote:

Originally Posted by

>Hi: Can any one please tell me about Packages and their use in SQL
>Server?

>
Packages are specific to the area of DTS (Data Transformation Services)
in SQL 2000 and SSIS (SQL Server Integration Services) in SQL 2005. SSIS
is the successor to DTS. Both DTS and SSIS permits you receive files and
transform them before you load them into SQL Server. Exactly how, I don't
really know, because I have not used any of them.
>
Some people, particularly those that maintain a data warehouse which
receives data from all over the place, work a lot with DTS/SSIS, but if
you like me spend your time with an OLTP system, you may never get that
far.

Unless the question is being asked from the perspective of someone that
has worked in Oracle in which case they do not exist.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.orgsql

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 error or hang in SQL Agent but not in BI IDE

Hi ,

I have an issues that worries me alot. I have SSIS packages scheduled in SQL Agent and they sometime error out or hang in Agent but never when i run them in BI IDE (Visual studio). There are two particular packages that always hang, alway in Agent but when i run them in BI IDE, they run just fine. Im was running them under in Agent under type= Sql server intergration services and also tried running under type = Operating System (CmdExec). Both types have the same problem. The packages hang. The log files dont say much, because the packages hang, no logging is doing so i dont get any infomation to determin what is going on. Can someone please help me ou.

Can you provide more information about the package:

What security settings are you using?

What tasks are in the package?

Where is the package hanging?

What log events do you have enabled?

Is it consistent? Happens everytime?

Does it happen if you run it under DTExec in the same credentials as under Agent?

Kirk Haselden
Author "SQL Server Integration Services"

|||The security settings of the package is EncryptSensitiveWithUserKey but no password
The task contained the the package are two data flow componenet ( in which there is a download from a remote source to local sql sever destination server ) , two execute sql task from which a sql statement is set to a varaible and assigned to the sql property of the datasources (using expression) in the data flow componenets and lastly , email task to send emails if the packaged failed or passed.
I have all the log even enable except for those in relation to the pipelines like OnPipelinePostEndOfRowset

It hangs everytime the packages runs in Sql Agent but not in VS (BI IDE)
I have only run it with dtexec under Agent and its still hangs.|||

This won't work because EncryptSensitiveWithUserKey uses the credentials of the user on the machine where the package is built.

Change the package protection level to "Rely on server storage for encryption".

Save the package to SQL Server and then schedule the package to run in Agent.

Should fix the issue.

Kirk Haselden
Author "SQL Server Integration Services"

|||is there any way i can do that without storing the packages in the db because i make changes to the pacakages very often and like it on the file system. If not, i can stomach storing it in the db.|||

Have you actually tried doing this on SQL Server 2005 SP1? BIDS will not let you save a package with 'Server Storage' ProtectionLevel. Therefore, I can't build a deployment manifest where I get the 'Rely on server storage for encryption' option. Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

|||

DawnJ wrote:

Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

this link describes how to save a package to sql server: http://msdn2.microsoft.com/en-us/library/ms137565.aspx

Packages error or hang in SQL Agent but not in BI IDE

Hi ,

I have an issues that worries me alot. I have SSIS packages scheduled in SQL Agent and they sometime error out or hang in Agent but never when i run them in BI IDE (Visual studio). There are two particular packages that always hang, alway in Agent but when i run them in BI IDE, they run just fine. Im was running them under in Agent under type= Sql server intergration services and also tried running under type = Operating System (CmdExec). Both types have the same problem. The packages hang. The log files dont say much, because the packages hang, no logging is doing so i dont get any infomation to determin what is going on. Can someone please help me ou.

Can you provide more information about the package:

What security settings are you using?

What tasks are in the package?

Where is the package hanging?

What log events do you have enabled?

Is it consistent? Happens everytime?

Does it happen if you run it under DTExec in the same credentials as under Agent?

Kirk Haselden
Author "SQL Server Integration Services"

|||The security settings of the package is EncryptSensitiveWithUserKey but no password
The task contained the the package are two data flow componenet ( in which there is a download from a remote source to local sql sever destination server ) , two execute sql task from which a sql statement is set to a varaible and assigned to the sql property of the datasources (using expression) in the data flow componenets and lastly , email task to send emails if the packaged failed or passed.
I have all the log even enable except for those in relation to the pipelines like OnPipelinePostEndOfRowset

It hangs everytime the packages runs in Sql Agent but not in VS (BI IDE)
I have only run it with dtexec under Agent and its still hangs.|||

This won't work because EncryptSensitiveWithUserKey uses the credentials of the user on the machine where the package is built.

Change the package protection level to "Rely on server storage for encryption".

Save the package to SQL Server and then schedule the package to run in Agent.

Should fix the issue.

Kirk Haselden
Author "SQL Server Integration Services"

|||is there any way i can do that without storing the packages in the db because i make changes to the pacakages very often and like it on the file system. If not, i can stomach storing it in the db.|||

Have you actually tried doing this on SQL Server 2005 SP1? BIDS will not let you save a package with 'Server Storage' ProtectionLevel. Therefore, I can't build a deployment manifest where I get the 'Rely on server storage for encryption' option. Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

|||

DawnJ wrote:

Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

this link describes how to save a package to sql server: http://msdn2.microsoft.com/en-us/library/ms137565.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 you
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
>
|||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>...[vbcol=seagreen]
> 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...
> a
> completed
> a
|||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...[vbcol=seagreen]
> 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...
open[vbcol=seagreen]
> a
> completed
computer[vbcol=seagreen]
after
> a
>

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

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
>...[vbcol=seagreen]
> 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...
> a
> completed
> a|||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...
open[vbcol=seagreen]
> a
> completed
computer[vbcol=seagreen]
after[vbcol=seagreen]
> a
>

packages and data source views do not work?

Hi,

doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.

1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries

When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.

The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.

How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?

Cheers,
Frank

You say you "added both OLEDB Connections to the Connection Manager." I am not sure exactly what you mean, but I wonder if you right-clicked in the Connection Managers tray and added an OLEDB connection. That would not help you use a Data Source View.

As the splendidly written and accurate article at http://msdn2.microsoft.com/en-us/ms403395.aspx describes through its links, you should in fact add a Connection from a Data Source: http://msdn2.microsoft.com/en-us/ms403395.aspx.

You can tell if you have a Connection Manager that enables access to Data Source Views by looking at the icon. If it looks like a Data Source icon, you're in business. If it looks like an OLEDB Connection Manager icon, then you will not be able to see any Data Source Views.

Donald

|||

Hi Donald,

thanks for replying. Your hint about the Icon that should be different led me to the right actions.

But regarding the quality and accurateness of the article I have to disagree.

Please look add the following quote taken directly from the article:
"After a data source and its data sources views are in the Integration Services project, you add must add a connection manager that references the data source to the package. When you add the connection manager based on a data source to a package, Integration Services adds an OLE DB type connection manager. Only sources, transformations, and destinations that can use an OLE DB connection manager can use data source views."

It tells that an OLE DB Type connection manager is added and that DSVs only work with OLE DB connection managers. There's no real usable hint, that I need to create some-kind-of-reference connection. From reading the article I thought I need to add the OLE DB connection that I used already for the DSV. "New Connection from Datasource" as an option is not quite clear in its meaning. Where's the difference to the normal OLEDB Connection? It looks to be linking to that OLE DB connection underneath anyway.

And one problem remains: As my DSV does have Named Queries pointing to both OLE DB datasource connections (2x datasource 1, 1x datasource 2) I only see the 2 first named queries using the connection manager but I never see the 3rd from the other datasource regardless if I also create a "New Connection from Datasource" from the second one and specify that one for the OLE DB Source. So heterogeneous DSVs are not supported?

Best regards, Frank

|||

The doc is indeed accurate, although maybe I should not overpraise its style. :-) The following extracts do seem very clear to me and even include a link to detailed info on how to add the data source.

... a connection manager that references the data source

... add the connection manager based on a data source

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

However, the reader is always right, and if you did not find the docs helpful, then that is fair enough. The information you needed was there, but you could not find it. It would be great if you could provide some detail on how you think they could have been better worded, or more clearly structured, to help us help others.

Meanwhile, you are indeed correct that you can only use one (the default) data source in a data source view with SSIS.

Donald

|||

Hello once again,

Donald Farmer wrote:

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

This is exactly one of the misleading statements. I shall add the data source on which the DSV was built. And for the DSV I have added two OLE DB Data Sources to the project. So my conclusion would be to reuse them. When I right-click the connection manager of the package I can choose between several types of connections to be added. And as the article also states that DSVs only work with OLE DB connections my first choice would be to select "New OLE DB Connection ...". Doing so pops up a dialog with the OLE DB Data Sources I have already defined in the project and used for the DSV.

Maybe this would be a better explanation:

Before you can use a data source view in a package, you must add a data source reference ("New Connection from Datasource") to the project data source object, on which the data source view is built, to the package. Your project data source object should be an OLE DB data source. For more information, ...

And for the future it would be nice if the SQL 2005 Development team could allow something like "New Connection to Data Source View" which would be (imho) very user friendly.

As for the restriction that only the Named Queries / Table Queries basing on the default data source in a DSV can be used in packages - a hint in the documentation would be very appreciated - and surely not only by me. Is that a restriction by intention or is it a bug going to solved in the future?

I highly appreciate your dealing with this topic

Kind regards, Frank

|||I agree that the documentation is not clear. I'm still struggling trying to get this setup.