Logging Packages Execution
Sep 13, 2006
Hello all,
I am struggling around defining a logging mechanism for my packages. I have 2 questions concerning that matter:
I have used event handlers for my loggings (as defined here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx ), but the problem is with packages that failed validation. I cannot find log entry for these cases since no "onerror event" doesn't trigger (for instance when the table I'm loading to doesn't exsist).
And the second question: many of my packages are executed using execute process task (using dtexec command line). I am trying to capture the result of the execution as a log file by using the ">" in the command line in order to output the execution to a log file in the following format:
dtexec /FILE "MyPackage.dtsx" > " MyPackageLog.log"
This works fine when executed by myself but when using the Execute Process task (defined: Executable: DTExec.exe, Arguments: /FILE "MyPackage.dtsx" > " MyPackageLog.log") I get execution error¦
Thanks,
Liran
View 11 Replies
ADVERTISEMENT
Mar 8, 2007
hello everyone,
I am going through the following URL in trying to understand logging from SSIS packages
http://msdn2.microsoft.com/en-us/library/ms138020.aspx
What I am confused about that the schema mentioned in this article is
Computer
The name of the computer on which the log event occurred.
Operator
The identity of the user who launched the package.
SourceName
The name of the container or task in which the log event occurred.
SourceID
The unique identifier of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred.
ExecutionID
The GUID of the package execution instance.
MessageText
A message associated with the log entry.
DataBytes
A byte array specific to the log entry. The meaning of this field varies by log entry.
Is missing certain things like event id, Type, category (these are usually seen for all messages in windows applicaiton event log). I feel that these are pretty important to be logged because based on these I will be setting up alerting mechanism.
Can the SSIS logging schema be changed so that things like eventid, type and category can be assigned at the time of logging the message?
regards,
Abhishek.
View 2 Replies
View Related
Sep 1, 2006
Hi!
I want to log package info like when the package starts and ends, and write info to a sql server table. there are of course many ways to do this. I just want some opinions from you if you have some clever ways to do this.
regards geir f
View 1 Replies
View Related
Feb 7, 2007
Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:
I currently have logging enable on several of my packages.
However, we are still in development of our packages and are reaching upwards
of 100 and logging will eventually need to be active on all of them. In
production, there will still be a development server and a production server,
both with different server names and user id/pwd.
I am looking for a way to dynamically change the logon information for the
logging so that we do not have to have someone go through and manually change
the options. I have tried using Dynamic Properties Task, but this only works on
the 2nd run of the package.
-----
As a second question: can anyone explain to me why the errordescription field
in sysdtssteplog is cut short?
View 1 Replies
View Related
Apr 21, 2006
Hi
I'vo got some trouble with the built-in logging feature and a self defined logging table.
Scenario :
A have a SSIS-package with enabled built-in logging to SQL-Server Data-Provider.
At the start of the package, i have an SQL Task which logs
the System Variable system::ExecutionInstanceGUID into the self defined logging table.
But the execution_id which the built-in logging stores to sysdtslog90 isn't the same
value like the value of the system::ExecutionInstanceGUID,
so i can't link my own table with the sysdtslog90 - table.
Any Ideas what is going wrong?
Thanks and sorry about my english
Ivo Becker (Switzerland)
View 3 Replies
View Related
Aug 1, 2006
Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package.
It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated.
Any ideas will be greatly appreciated.
View 5 Replies
View Related
Mar 14, 2000
Can anyone tell me how to turn logging on to get statistics on execution times for stored procedures
Thanks
View 2 Replies
View Related
Oct 13, 2005
Dear group,is it possible in SQL-Server to see when a stored procedure wasexecuted ?I would say it is only possible with some traces but not with thestandard settings.For a short answer on that matter i'd be thankful.RegardsUli
View 4 Replies
View Related
Apr 6, 2006
Hello,
I am experiencing that my SISS packages are executing faster during development from within the Sql Business Intelligence Development Studio environment than when I execute them with dtexecui after I have deployed them as either File System Or Sql Server deployments.
After deployment the packages run about twice a long as they do from within Sql Business Intelligence Development Studio (i.e they reference the same data and are executed on the same machine).
In short the packages import data, during this import stage the data is massaged certain calculations are performed thereon where after this massaged data is used to perform further calculations.
A summary of tools used in SISS are Execute Sql Task, Flat File Source, Oledb Destination, Data Conversion, Aggregate, Conditional Split, Merge, For Each Ado Enumerators, Script Tasks, Derived Columns and Recordset Destinations.
In terms of time from within Sql Business Intelligence Development Studio when the packages are executed for 1 month's data the total combined time is about 10 minutes and when deployed as either File System Or Sql Server deployments they run for about 20 minutes. My problem is when they are executed for a years data instead of running for 120 minutes they run for at least 240 minutes or longer.
Is this normal or am I missing some settings when building the package for deployment?
Luke.
View 3 Replies
View Related
Jun 8, 2006
Hi
I'd like to know if there's a way to control the execution of ETL packages, such that:
Different packages, or at least packages that don't access the same table or database run asynchronously with respect to each other; e.g., two different packages run at the same time
and
If a package is called for execution more than once by different requests, force them to run synchronously, or one after the other.If this is possible, what resources would it require? Is this possible under, say, a dual or quad processor machine?
Thanks.
View 6 Replies
View Related
Jan 11, 2006
Hi,
Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?
What is the recommended way to monitor SSIS execution?
Thanks,
- Joel
View 2 Replies
View Related
May 30, 2006
Is anyone executing SSIS packages using a web service similar to the example in http://msdn2.microsoft.com/en-us/ms403355(SQL.90).aspx
From what I've read, there is a new HTTP server embeded in SQL Server (so we don't have to have IIS) that this could be done from??
View 2 Replies
View Related
Nov 6, 2006
Our SSIS packages use the Web Service Task to call services to send email and write package failure data to a department wide database. These Web Service Calls are failing with HTTP 401 errors. It was caused by the passwords for the HTTP connections not being saved when the SSIS pkgs were saved to .dtsx files. I have tried saving the package with a password and the EncryptSensitiveWithPassword protection option. This password can be supplied when the package is called from another package or a package is executed in Visual Studio but cannot be supplied to DTexec to execute the package in a job. DTexec does have a /Password parameter but it is rejected if the package is loaded from a .dtsx file.
This appears to be a bug in DTexec. It only accepts the /Password parameter when the package is loaded with the /SQL option. Specifying /Password and /File is not supported.
Is this a known bug? Are there any workarounds?
Has anyone successfully called a Web Service from SSIS executed via DTexec?
View 1 Replies
View Related
Jun 18, 2007
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
View 3 Replies
View Related
Oct 17, 2007
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
View 5 Replies
View Related
Sep 12, 2005
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies
View Related
Aug 23, 2007
after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Dec 7, 2005
Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies
View Related
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
View 4 Replies
View Related
Aug 2, 2006
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
View 1 Replies
View Related
Nov 29, 2004
Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
View 3 Replies
View Related
Jul 7, 2006
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies
View Related
Dec 6, 2006
How can I check from database username and password? It doesent need any special authentication, just a lookup through the database and if the user exist than continue with the next page.Thanks
View 2 Replies
View Related
Oct 1, 2004
I have a web application accessing a SQL Server database (the ususal stuff).
I want to be able to log who did what on which table. I need to display this information on the web application. Is there an easy way of doing this, rather that making duplicates of a lot of data?
The best way I have thought of so far is making a new table with the following fields:
Table_Changed
Table_Primary_Key
Old_Field_Value
New_Field_Value
User
Date_Changed
Every time someone changes something, it is logged in this table, so that, at any time, I can display who changed what.
I have one more question. If I do do it this way, is there a way of getting the primary key value of any table? E.G. could I do something like this_table.primary_key.value ?
Jags
View 3 Replies
View Related
May 30, 2001
Is there a way to produce a log of all SQL statements hitting a database in a given range of time by a specific SPID? Obviously the SQL Server activity logs do not go into that much detail, except when errors are produced or a change is made to a system table. Is there a setting to add more detail, or to log a specific SPID's actions, or maybe a third party software that will give me what I am looking for?
Thanks. JT
View 1 Replies
View Related
Aug 22, 2000
does SQL 7.0 have any built in logging capabilities to identify row level actions by operator. For instance, can it tell me that a particular user deleted or inserted a row? How would I tell it who the operator is?
View 2 Replies
View Related
Jun 11, 2004
Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?
View 2 Replies
View Related
Dec 9, 2004
Hi,
is there any chance to log all incoming SQL-statements with date/time and duration ?
Thx for help.
dajm
View 2 Replies
View Related
Aug 20, 2007
Is there anyway to log every incoming command, INSERT/UPDATE/SELECT/DELETE to a seperate database table?
Thanks
View 4 Replies
View Related
Mar 27, 2008
I've been asked to write a trigger that will basically log changes to certain fields in certain tables, then create a front-end application where the user will be able to review the info. The front-end app. is not a problem for me - the trigger is. I have found example of how to do this on Update when it's a complete row you want to log, but not a specific field. In addition, I also need to know if someone is attempting to read certain data and who that user is. If the user is not someone that is allowed to read the data, then I need to send an email alert.
I believe it's possible to do the above (despite my lack of knowledge :) -
Does anyone know where I can get more information on how to accomplish the above - or where to start looking?
Thanks to any who can guide me in the right direction.
View 1 Replies
View Related
Dec 16, 2007
Hi,IĀ“m currently playing around with ASP.NET.Is there a way to log all Queries that are send to the SQL-Server? Something like the query.log of a Mysql.
View 3 Replies
View Related
