DTS-Package Sends Mail And Files Before The Job Finishes
Jan 11, 2008
I have a package which truncates files, fills them with data and
should send a mail with the files as attachment when it finishes
running. In between, when the task is still running mail is sent with
empty files. When I take a look at the directory (at the end of the
task), where the files are created I find out that the files are not
empty.
(e.g. in task: doA->doB->doC->SendMail. But instead of waiting till
doB->doC finishes, it does the following: doA->SendMail ... may be
because doB takes long.)
Can I indicate anywhere that it should delay the mail until at the end
of the task?
I am having a few problems with Database Mail and wondered if anyone could assist. It sends test mails fine, but when I run the following script:
(I've changed my email address in this post to test@test.com they are accurate in the scripts.)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test Mail',
@recipients = 'test@test.com',
@body = 'Sent by sp_send_dbmail',
@Subject = 'SQL Server Email Test Email';
It gives the following errors.
(from the above script)
mailitem_id = 16 profile_id = 2 recipients = test@test.com copy_recipients = NULL blind_copy_recipients = NULL subject = SQL Server Email Test Email body = Sent by sp_send_dbmail body_format = TEXT importance = NORMAL sensitivity = NORMAL file_attachments = NULL attachment_encoding = MIME query = NULL execute_query_database = NULL attach_query_result_as_file = 0 query_result_header = 1 query_result_width = 256 query_result_separator = exclude_query_output = 0 append_query_error = 0 send_request_date = 2008-04-15 10:50:03.827 send_request_user = COMPANYTest.Test sent_account_id = NULL sent_status = failed sent_date = 2008-04-15 10:50:04.000 last_mod_date = 2008-04-15 10:50:04.513 last_mod_user = sa
(from the test mail)
mailitem_id = 11 profile_id = 2 recipients = test@test.com copy_recipients = NULL blind_copy_recipients = NULL subject = Database Mail Test body = This is a test e-mail sent from Database Mail on UKDEVSQL1 body_format = TEXT importance = NORMAL sensitivity = NORMAL file_attachments = NULL attachment_encoding = MIME query = NULL execute_query_database = NULL attach_query_result_as_file = 0 query_result_header = 1 query_result_width = 256 query_result_separator = exclude_query_output = 0 append_query_error = 0 send_request_date = 2008-04-15 09:51:46.530 send_request_user = COMPANYTest.Test sent_account_id = 4 sent_status = sent sent_date = 2008-04-15 09:51:46.000 last_mod_date = 2008-04-15 09:51:46.610 last_mod_user = sa
sysmail_event_log gives this error for mail item 16 (the scripted email):
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2008-04-15T10:11:41). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Requested action not taken: message refused). )
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
I began installing SQL Server 7.0 the other day on a standard NT Server. When I got to the installation options, Enterprise Edition was the only server option. It wouldn't install on standard NT. Nothing on the box, in the box, or printed on the cd indicated that it was the Enterprise Version. The error message says something about not being able to install on this server from this CD.
We called MS and they did admit to sending an unknown number of CD's out with the wrong setup. I just thought I would pass that on to those of you who might be questioning your own sanity.
Good luck getting the replacement copy. They are telling us that it will be 4+ weeks before they overnight our copy to us. I won't even try to figure out why it would take that long to get a copy. Let us all know if any of you have run into this. I would like to see how common this is.
I have several DTSX packages which send out report files by mail. For security purposes, the SMTP server can only be reached from specified IP addresses, from specified processes, with specified sender address. I know all this information, except the process name that sends out the emails in a DTSX package. So my question is, what process can be identified as the one that sends out the email?
We have SQL server 2005 Reporting Services SP1. In Report Manager I have a shared subscription that runs every hour between 8am and 6pm (I created the shared subscription in Report Manager, then modified it in Sql Server Management Studio to run the hours I wanted). I have a second shared schedule that runs Tuesday and Friday at 7am. For 2 separate report subscriptions - one uses the hourly schedule and the other uses the 2-day schedule, the first time they run during the day it sends 2 email - after that it sends only 1 email, as it should. I created a subscription to email a report when the report content is refreshed from a report snapshot. In Properties...Execution, I selected to render this report from a report execution snapshot, and selected my shared schedule. For the History property I have selected Allow report history to be created manually and Store all report execution snapshots in history; I am not using a schedule to add snapshots to report history in the History property.
When I look at the job in Sql Server Management Studio, it has only ran once each hour. When I look at the snapshot history, there is only one record for each hour. Why is the first scheduled instance sent twice instead of once?
Can anyone assist me? I am trying to create a trigger within our local database that will allow me submit an email to a local exchange server. Its an internal email that our company has created for responses by candidates when the original stored procedure meets a condition of TRUE.
Is SQL 7.0 capable of sending emails to a specified address through a Trigger? I was told it could but have yet to come across any documentation backing this up.
Would anyone know the generic syntax I can use to create such a trigger?
Any suggestions would be appreciated.
Thanks in advance, Claude Johnson cjohnson@staffmentor.net
I'm having some trouble with a new SQL 2005 Server. Database mail is configured/enabled. I've used SSIS to create a package that exports data to an Excel spreadsheet and e-mails the file to a user (currently, me).
The Send Mail Task will send a message with the file attached from SSIS while debugging the package but once the package is compiled (no errors compiling) and executed from a job, I don't get the mail message. SQL Server Agent reports successful completion of the job.
I decided to add the Send Mail step as an Execute SQL Task which calls the sp_send_dbmail proc. I can send mail with the attached file from Query Analyzer using sp_send_dbmail. It sends the message successfully in debug mode within SSIS as well. However, once compiled and deployed, it fails to send the message when called from a job. Again, SQL Server Agent reports successful completion of the job but the e-mail never comes.
MSSQL and SQL Server Agent are running under a domain account that is a local admin and member of the sysadmin role.
sp_send_dbmail is using a public profile in Database Mail.
I need to make some changes to tables in a database. To do this requires that I "alter table" and "drop constraint" All constraints drop properly, except one. The last one will churn away forever in query analyzer and never drop. I am unable to make the necessary changes until this completes. I am guessing that there is a data issue that is keeping the constraint from being dropped. Any ideas on how to proceed? Thanks!
I have an activex script which executes a query that never finishes. If I recompile the query in Management Studio before I run this step, then the step will *usually* work fine. This is the last step in a very long update process so the data has changed a great deal when this step executes.
The query always works fine in Management studio btw.
I am using BI studio to run the package.
I have tried placing a step before this that marks the query for recompilation but that doesnt seem to work. Any ideas how to resolve this?
I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.
I have a DTS package in SQL Server 2000 that has a Send Mail task to shoot me an email when it completes. When I tested this manually it ran fine however when it runs as a scheduled job in the SQL Agent the Send Mail task fails.
Prior to being able to send emails I installed Outlook so I could build a MAPI profile. With the MAPI profile there I am able to create and at least manually test a Send Mail task. From experience I believe this issue arises due to the fact that when I'm logged in Outlook is running but when I'm logged out of the Server, it is not running and so the task fails.
I had a DTS package on sql2000 which i migrated succesfully to Sql2005 and im able to open the package and execute the package.Now i want to add a new database mail component on this package to send emails to recepients.In short i dont want to use SQL Mail component of Sql2000 which required outlook components,instead i want to use the new features of SSIS to my package which was designed on sql2000. Is it possible to use the SSIS new features to be incorporated on my old DTS package?
I have to create a task, and in that task i have check exection time of package and if it more then specified time, send a mail with custom message to some specific users.
or
Can i write a event which occur after specific time and send a mail?
In my project i need to call a child package to send email to a group of users. All the configurations for sendemail task such as to, from, subject, attachment, and body are set using global variables. I pass all those variables from parent package and this is workin fine. Now my problem is the BODY MESSAGE. I need to have the body message which looks like the one below.... Status of PCCODS database on (PRODSQL6): TimeStamp on database: 10/25/2006 6:51:32 PM FACILITY OPEN/CLOSE STATUS -------------------------- -- Number of facilities open for month 10 is 80
(See attached PCCCLoseStatus excel file for details on closed facilities.) Here u need to look at Timestamp, some values like '10', '80' , 'PRODSQL6' are all passed through a different global variables. This is my problem! I already have a workin project that works fine if i pass just one line of body message. Now follows the issues 1) I cannot enter more than one line in the variable value column(if it's one line then it will work fine!!!!) 2) I need to get some values from parentpackage global variables displayed in the email. 3) I need to have a body message that looks like above one. I really doubt whether i could implement these by just passing all these in one global variable or i should have a script task, i donno..... may be someone can help me do this!!! How can i have this kindof body message implemented in SSIS? Suggestions are greatly appreciated....
as declare @From varchar(8000) declare @Subject varchar(8000) declare @Body varchar(4000) declare @smtp varchar(8000) declare @counter int, @tbl varchar(8000) Declare @MailID int Declare @hr int Declare @To varchar(8000) Declare @tblquery varchar(8000) declare @id int, @deptemail varchar(8000), @tmpmth varchar(8000), @usedb varchar(8000) set @from = 'name@mail.com' set @subject = 'testheader' set @body = 'testing successful' set @smtp = 'smtp.com'
--========================================================================= --============================ get database name ======================= IF (LEN(MONTH(GETDATE())) = 1) BEGIN Set @TmpMth = '0' + CAST(MONTH(GETDATE()) AS varchar(2)) --01 END ELSE BEGIN Set @TmpMth = CAST(MONTH(GETDATE()) AS varchar(2)) --12 END SET @UseDB = 'DATA' + CAST(YEAR(GETDATE()) AS varchar(4)) + @TmpMth --aia_DATA200712 --=================================================================== --============================ get table number ======================= set @counter = 1 while @counter >= 59 begin IF (LEN(@counter) = 1) BEGIN Set @Tbl = '0' + @counter --01 END ELSE BEGIN Set @Tbl = @counter --12 END
--=========================check if table being created exists IF EXISTS (SELECT 1 FROM information_schema.schemata WHERE catalog_name = 'temptable') GOTO table_1 --=================================== get all email accounts ===================== set @tblquery = ' select ID, Email INTO temptable FROM ( select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.inward' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.local' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null UNION ALL select distinct d.id, d.email from tt32_aia.dbo.department d right outer join tt32_aia.dbo.extension e ON (e.parentid = d.id) right outer join ' + @usedb + '.dbo.other' + @tbl + ' data ON (e.extn = data.extn) where callclass = '''' or callclass is null )data DECLARE deptemail_cursor CURSOR FOR select id, email from temptable where id is not null '
--============================================================================= --====just above you can see the cursor.. below is sending the emails --======================================================================
exec(@tblquery)
OPEN deptemail_cursor
FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail
WHILE @@FETCH_STATUS = 0 BEGIN --If LEN(@deptemail) > 0 --BEGIN set @to = @deptemail EXEC @hr = sp_OACreate 'CDo.message', @MailID OUT --CDo.message |CDONTS.NewMail <-- different mail server EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' EXEC @hr = sp_OASetProperty @MailID , 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtp EXEC @hr = sp_OASetProperty @MailID , 'From', @From EXEC @hr = sp_OASetProperty @MailID , 'HTMLBody', @Body EXEC @hr = sp_OASetProperty @MailID , 'Subject', @Subject EXEC @hr = sp_OASetProperty @MailID , 'To', @to EXEC @hr = sp_OAMethod @MailID , 'Send', NULL EXEC @hr = sp_OADestroy @MailID --END FETCH NEXT FROM deptemail_cursor INTO @id, @deptemail END
CLOSE deptemail_cursor DEALLOCATE deptemail_cursor
Table_1: drop table temptable return
set @counter = @counter + 1
end
this is suppose to send email automatically to every email account that it will get from all the tables (around 250 tables). the problem is its not sending, but if i try to take my code outside of the "SET @COUNTER = @COUNTER + 1 END" and close the if statement above, i can produce the correct result.. i'm thinking maybe its the positioning? but, there could be some overhauling needed to do with this.. sorry for posting the sp. sorry for the trouble.. please help me
i have a package that contains a foreach loop container, in this container i have sql tasks, and execute package tasks, that end with a send mail task. if there something wrong with the smtp server, or it's down, the send mail task fails the package. i don't want this to happen, what i want that if the send mail taks fails, the package will continue it's execution.
--i thought of using the event handler... but i don't know if it works...
Here is the situation I'm trying to set-up a package to ftp some files down if that part fails email me but if successful rename the 2 files to something else.
so what I set-up was a FTP tasks with the remote path set as a expresion since I needed to Download todays files. no problem there have that part working.
so then I set a notifaction event with failure constraint to email me. pretty easy so far.
then I set 2 tasks to rename the 2 files downloaded I'm sure I could do this part with a loop but what seperate tasks so I knew if any failed or could set-up notifaction tasks if I wanted. in the 2 rename tasks I set the sourcepath to an expression so it would know to read todays files and they could change from day to day.
then the destination part is hard coded for now to a file name.
now here is were the issue comes in I go to save it and it says the files don't exist from the rename piece. well of course not they haven't been FTPed yet.
so I tried delayevaluation on both the file system tasks but a no go still errors.
What I need it to do is basically say wait till the ftp is run these files won't be there. any help in the right direction would be appreciated.
I receive the following error when trying to invoke xp_sendmail 'xp_sendmail: failed with mail error 0x80070005'. However, for alert notifications the email works fine. SQL is using an alias account to send mail. This account doesn't have any funny characters. Any ideas why the alert system works but xp_sendmail does not? The invokation of xp_sendmail is being done by SA.
FROM MQIC.DBO.OBSERVATION_F INNER JOIN MQIC.DBO.DATE_D
ON MQIC.DBO.OBSERVATION_F.DATE_KEY = MQIC.DBO.DATE_D.DATE_KEY
it runs to completion in about 15 min - during the entire time there is extensive used of CPU from Task Manager.
If I do the above statement, it seems to use lots of resources (50% +) for about 5 min, then falls to 5%. It just seems to sit there, for an hour + at which time I've killed the query.
This is actually the second time I tried this. The first time was on a different machine, with the P4, 3GB RAM, plenty of disk space, and using SQL-Server 2005 Standard - SP1. Exactly the same decrease in resources happened, and even though it ran several hours, no results.
Any thoughts here - not waiting long enough, memory leaks, etc.?
I have a query which used to run fine on a rubbish SQL 2000 box in about a minute, but with SQL 2005 (SP2) it never finishes, even when left overnight. No errors in the logs or anything. It is the same database which was backed up from SQL 2000 and restored into 2005. Does anybody have any ideas?
Cheers Steve
SELECT DISTINCT R1.RowVersionId, R2.EnumID AS A, R2.EnumID AS B, R4.EnumID AS C, R6.EnumID AS D, R8.EnumID AS E, R10.EnumID AS F, R12.EnumID AS G, R14.EnumID AS H
FROM
RowRuns AS R1
INNER JOIN XRunConfigEnum AS R2 ON R1.RunVersionID = R2.RunVersionId
INNER JOIN RowRuns AS R3 ON R1.RowVersionId=R3.RowVersionId
INNER JOIN XRunConfigEnum AS R4 ON R3.RunVersionID = R4.RunVersionId
INNER JOIN RowRuns AS R5 ON R1.RowVersionId=R5.RowVersionId
INNER JOIN XRunConfigEnum AS R6 ON R5.RunVersionID = R6.RunVersionId
INNER JOIN RowRuns AS R7 ON R1.RowVersionId=R7.RowVersionId
INNER JOIN XRunConfigEnum AS R8 ON R7.RunVersionID = R8.RunVersionId
INNER JOIN RowRuns AS R9 ON R1.RowVersionId=R9.RowVersionId
INNER JOIN XRunConfigEnum AS R10 ON R9.RunVersionID = R10.RunVersionId
INNER JOIN RowRuns AS R11 ON R1.RowVersionId=R11.RowVersionId
INNER JOIN XRunConfigEnum AS R12 ON R11.RunVersionID = R12.RunVersionId
INNER JOIN RowRuns AS R13 ON R1.RowVersionId=R13.RowVersionId
INNER JOIN XRunConfigEnum AS R14 ON R13.RunVersionID = R14.RunVersionId
WHERE
((R2.ParamID='ee72510e-3bab-49f6-1ff9-4d09cbe8670a' AND (R2.EnumID = '1a2868fb-72ef-e1d3-e79d-fbb5814ab481')))
AND
((R4.ParamID='7aadb3a4-3d13-8e0d-bfa4-4243ed1fdb35' AND (R4.EnumID = '745fb00c-0b16-7b4e-bf8f-da0f46777ca0')))
AND
((R6.ParamID='8c9aee3a-df1f-6ec5-131a-8fa0309ce1ff' AND (R6.EnumID = 'c7af1456-56bc-ba9c-f1e4-95cfd5542d10')))
DML T-SQL works fine. But When I try to run SELECT query with joins that involves large tables (1.5 mil rows in each) in 2 databases, it instantly goes into "runnable" status and never finishes. It never finishes even when I have top 1 clause included. When I run insert or update that involves same tables, it works just fine.
Server facts:
W2003 SS 2K5 SP2 Db Comp level 90 (for all db) MAXODP = 1 (8 processor box)
Recently moved databases from server that had SS 2000.
When this query was running on box with SS 2000, it took about a minute to finish, but it completed.
I already tried: changing MAXODP settings, using OPTION (maxdop 1) etc.
hi, all; i'd like to resolve this problem: We are Developing packages in Dev.Server; if we are Deploying our packages to Prod.Server; we need to modify the connection string and related properties of the connection manager. 1. How to modify the connection manager while deploying to production server? 2. can we create 2 dtsconfig file for a single package; is there any mechanism for instructing sequel server to use a particular dtsconfig file for our package.
i modified the config file in order to change the connection string. but its affecting only the config file not the package. while running the package its reffering the old connection string only.
I created a project with multiple packages. I created a global config file that all packages reference. I also created a config file for each package that contains the package specific settings. I checked it all into TFS. I went to a different machine and checked it all out. Upon loading a package into the IDE I got the following 3 warnings.Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "MT_LSE_PROD_StageLoad.dtsConfig" cannot be found. Check the directory and file name. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsx Warning loading MT_LSE_PROD_StageLoad.dtsx: The configuration file "Environment.dtsConfig" cannot be found. Check the directory and file name. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsxWarning loading MT_LSE_PROD_StageLoad.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. e:contentloadersprint1aMT_LSE_PROD_StageLoad.dtsxAny ideas??? And yes, the files *ARE* there! I tried building the project and it builds. I also tried doing a deployment build and that too built. I am so confused!!!
It's very simple import, without any data modifications; from severalexcel files to one table.I tried the wisard and it gives me selection for only one file.I am not used to create DTS packages and schedule jobs, so I would needsome help.Thank youInna
I am working on on SQL Server Integration Services and facing few problems in it.
Actually am supposed to create a package that would automatically pick excel spreadsheets with a specific format and import it onto the SQL server. (Lets say , there is a company named AB and they have got various products named CD,EF and GH and each product has its own spreadsheet in which its monthly sales(JAN, FEB,...NOV, DEC) is given. So i have to build a generic package for each product (lets say CD) so that i don't have to import every spreadsheet seperately for each month. To summarize i just have to build a package where i can deploy the various spreadshhets again and agian instead of building a package for each and every month spreadsheet seperately.
I have tried and used lots of combinations like Loop conatiners etc. but still am not able to find a solution to it.
I have created a job that will execute a SSIS package which will unzip some zip files. For unzipping we are using WinZip. In the package I have used a .Net script task for unzipping. This script is using WZUNZIP. When I am executing the package directly it is unzipping all the zip files. But when I am executing the job that will execute the SSIS package for unzipping it is going on with the execution and not unzipping the zip files. So finally I stopped the job.
