Logging/Email Of Bad Records
Jan 22, 2008
My SSIS tasks will have numerous inputs (flat files, Excel and Database tables). At the end of each task I want the task to send an email that lists the records that failed to load. I have looked at BOL and the forums and have not found what I am looking for. I am reluctant to create flat file outputs for each type of input due to the number of inputs. I am looking to have the error records as a string in a flat file or in an email. I am familiar with the CreateObject("Scripting.FileSystemObject"), createtextfile & writeline methods in VB. However, I am not sure how I would implement this. This is because the file creation would have to be done in a script task and the data rows loaded in a script component. If anyone can give me some suggestions, I would appreciate it.
Thanks in advance,
Lee
View 8 Replies
ADVERTISEMENT
Feb 13, 2015
Initially I had designed ETL using Dataflow task in SSIS , No I have converted into Store procedure using merge statement.
I am getting new records inserted, source records and deleted row count when I am running sqp manually.
In ssis simple I have used the rowcount transformation to capture the records.
below variable i have declared in SSIS Package.
User::etlArchiveLogId
User::sourcerecords
User::newrecords
User::changerecord
now I am incremental loading using Stored procedure below is the sp which is executing when task success and logs the records.
ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)
[code]....
Now I want to log the variable records which I have declared in my stored procedure .how can I log it in table?
View 1 Replies
View Related
Mar 30, 2008
is there a way to set up a scheduled sp to email me if no records are added to a certain table within the hour?
is this possible?
View 1 Replies
View Related
May 10, 2007
ms sql server
data:
idno Email Category
1 a@a.com c
2 b@b.com c
3 b@b.com c
4 b@b.com c
only unique email address record are requierd.
1 a@a.com c
2 b@b.com c
what could be the query for such requirements.
View 5 Replies
View Related
Mar 29, 2004
hi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.
View 3 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
Jul 6, 2015
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies
View Related
May 4, 2007
Under IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Any idea?
thanks
View 2 Replies
View Related
May 12, 2008
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
View 1 Replies
View Related
Jul 12, 2007
Hello ALL
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
Please help me with the best way to do this
Thanks in advance
View 6 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 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
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Aug 6, 2007
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
View 7 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 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
Dec 16, 2003
I have a problem acessing MSDE. My server runs at "NT AUTHORITY/NETWORK SERVICE" so it is not allowed to connect through the windows authentication feature. It seems that the password for the "sa" user account was changed during setup, after reading the logs from the setup I can see that it was changed but I cannot see what it was changed to. How would I set up a new account that I could use to access the server though SQL Authentication???
David Legg
View 1 Replies
View Related
Oct 20, 2005
Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to
change DB1 into DB2. This means recording both what happened in the GUI
and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log
somewhere. Is there a way to set this to output all the changes made
from a set date on a database into a SQL log file?
Thanks in advance for any help.
Jagdip
View 3 Replies
View Related
Jan 31, 2002
Other than doing bulk insert is there a way to insert in SQL 7 without logging the transaction in order to speed up the process?
If so what command(s) allow this?
View 1 Replies
View Related
Jan 29, 2001
If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
Thanks,
Veronica
View 1 Replies
View Related
Nov 8, 2001
I have (amongst many) an overnight SQL stored procedure that occasionally takes over an hour to run compared to its usual 2 minutes. It does the same each night, as does everything around it. (N.B. The job truncates and reinserts data into a table.)
I'm just wondering if a user request during the day that reads the table perhaps leave the PID open and thus a lock open with it. Then, perhaps my SQL job comes along and waits for the lock to release before it performs the truncate.
Couple of questions:
1. Could this happen?
2. If so, is there a log anywhere to suggest that a deadlock situation arose and PID x was the loser?
Thx in advance.
Andy Richardson
View 2 Replies
View Related
Sep 25, 2002
Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.
I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited
I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails
(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)
So my question is
What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping
Thank you
Alex
View 1 Replies
View Related
Nov 22, 2002
I need to logg progress of a DTS job. How do I do that? I'd like the progress to be stored in a file and emailed to me.
I need to track progress of the transfer of each table in two databases.
View 2 Replies
View Related
