Hi
I'd like to access to a SendMail object with an ActiveXScript.
I access it by the Tasks property of the package object.
I can retrieve the name or the description but I cannot access to the specific properties of the SendMail object (fileAttachments, ccline, ...)
How can I do it ?
THanks
hi,
at the end of my ssis package flow, i want to invoke a sendmail task.
now, when configuring the sendmail service, i see i can only enter the name of my smtpserver. however, i see the task fails, probably because a user and a password is missing (i have it, but don't see where to type it in the SSIS).
(i want to use the smtp server of my internet provider)
anyone can help?
I have a sendmail task following a data flow task. How can I set "To" attribute on the sendmail task before running it ? for example, read a config table and detemrine who the mail should be sent to...
View 1 Replies View RelatedDoes exchangeServer accept in SendMail task?
only SMTP?
who knows?
For internal mail server , it's only via exchange server.
Hi all--I am writing a DTS package looking to back up all databases in sequence from a specific instance of SQL Server. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of SQL tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure of a particular database to back up to file, execute the following SQL task:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
SELECT @srvname = @@servername
select @db_name = db_name()
set @msg = 'Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
SELECT @srvname, @db_name, @msg
(Many thanks to Whitney Weaver and SQL_Menace for help with sections of this code)
Upon success of #1 and/or #2, go on to the next database, repeating cycle of the SQL tasks above for the next database in the task. For example, this logic goes like:
1. Back up master database; if fails, send email in SQL task; if either task is successful, goto database #2;
2. Back up model database; ...<etc>.
My problem is the current database set in @db_name. I apparently need to reset this variable for each database that gets backed up, and I can't seem to get these set as values to be passed from SQL Task #1 to SQL Task #2. Any suggestions on how I might achieve this?
- Jonathan
Hi,
I have a sendmail task in my ssis package. In the From field, if i hardcode my from and to address as user@company.com it works fine and mail is sent. But i want to read the from and to values from a database table. SO i defined the package level variables and getting the values into the variables. SO at From if i specify the variable name it gives a compiel time error"the address in th from line is mal formed. it is either missing @ character or not valid".
how do i specify teh variable for from address? I feel, may be i can use expression, but i don't know how to do that.
Can anyone help me with this?
Thanks in advance,
I have multiple sequence containers in my package. Â I only want to have one sendmail task for the failure/completion of the package. Â If I put the sendmail task in the last sequence container and the first seqence fails, the sendmail task will not be reached and therefore, no email will be sent out.Is there a way to have one sendmail task for all the sequence containers and allow it to send mail regardless of what sequence fails/completes?
View 4 Replies View RelatedWhen I am executing the following sendmail code,it is apending the results to the mail.But I want the results to be sent as attachment,in .txt format.
exec master..xp_sendmail
@recipients='Tpolasp@hotmail.com',
@query='c2usa..sp_report_daily_pc',
@attach_results='true',
@subject='Daily fastquotes'
Interestingly,even if I use @attachments option by giving a specific file name,then also it is sending the mail in which the contents of the attached file are appended to the mail!
Is there anything that I shall configure somewhere to make it behave properly?
I am using 7.0 standard edition with no SP.Any help is greatly appreciated.
Thanks.
hi
how to sendmail in sql2000
--------------------------------------------
Declare @filename char(22)
Declare @cmd char(100)
Select @filename = '"' + 'D:Feedsest_' + substring(convert(char(12), getdate(), 12), 3, 4) + substring(convert(char(12), getdate(), 12), 1, 2) + '"'
Select @cmd = "xp_sendmail @recipients = 'test@test.com'
, @attachments = " + @filename + " "
exec(@cmd)
Go
--------------------------------------------
Above T-SQL step is causing the following error:
Server: Msg 103, Level 15, State 7, Line 6
The identifier that starts with 'xp_sendmail @recipients = 'test@test.com'
is too long. Maximum length is 128.
Any help??Thanks.
When I am executing the following sendmail code,it is apending the results to the mail.Actually, I want the results to be sent as attachment,in .txt format.
Any help is greatly appreciated.
exec master..xp_sendmail
@recipients='Tpolasp@hotmail.com',
@query='c2usa..sp_report_daily_pc',
@attach_results='true',
@subject='Daily fastquotes'
Thanks.
I have a step like this: I am getting the following error:How to code @attachments in quotes?? thanks.
The name specified is not recognized as an
internal or external command, operable program or batch file.
(2 row(s) affected)
Here is my T_SQL step:
Declare @filename char(22)
Declare @cmd char(100)
Select @filename = 'D:Feedsest_' + substring(convert(char(12), getdate(), 12), 3, 4) + substring(convert(char(12), getdate(), 12), 1, 2)
EXEC xp_cmdshell @recipients = 'test@test.com'
, @attachments = @FileName
I have setup SQL Mail on a SQL Server 2000 (service pk 3a) with outlook 2002.
SQL Mail give me the success message when I test the profile.
When I send a test message to an operator from SQL Agent it works fine.
When I send a message from the Outlook 2002 client (logged in as the Service-SQL domain account), I can send a message without a problem.
However, when I send an xp_sendmail message (xp_sendmail @recipients='email@address',@message='test',@subje ct='test') it tells me 'Mail Sent' in the result window, but the email never arrives or shows up in the 'sent items' folder in outlook 2002.
Anyone else have this issue.
Thanks - Rick
For example the result of an Execute T-SQL Statement task.
This task does not have a hook-up for variable assignment, the SendMail can take in a variable. How do you pass the result of EXEC T-SQL statement be assigned to the variable in the SendMail task?
Hello All,
I have an Execute SQL Task and a SendMail in the control flow of my package.
If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.
But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.
How can I achieve this...
Thanks,
Kapadia Shalin P.
Hi, with some help today I was able to get my stored procedure runningand have the results emailed to me. However, this is how its showingup:Accounting_Year WK_IN_FYEAR LocationGL_AccountCol Data Difference--------------- ----------- ------------------------------------------------------------------ -------------------- --------------------2007 49 Test1500-001-2587872.0200 -2587872.0200 .00002007 49 Test2500-001-3344713.5000 -3344713.5000 .00002007 49 Test3500-001Is there anyway to line them up side by side properly? When i have twocolms selected the format comes out ok. Thanks for all the helpagain!Here is the sp:CREATE PROCEDURE [dbo].[spEmailVariance](@SubjectLine as varchar(500),@EmailRecipient VARCHAR(100))ASDECLARE @strBody varchar(5000)set @SubjectLine = 'Weekly Flash Update'SET @strBody ='Select statement'exec master.dbo.xp_sendmail@recipients= 'XX@XXXX.com',@subject= @SubjectLine,@query = @strbodyRETURNGO
View 2 Replies View RelatedHow do I Convey Information using Notification - can someone please explain?
View 4 Replies View RelatedI am trying to send a csv file with 15000 records via the database mail in SQL Server 2014. The problem is that when I open my email the csv only contains 209 records. I have tried the same thing in SQL Server 2012 and it works as expected - it sends the 15000 records in the csv.
I have tested this on several sql servers with 2014 edition on them, and I have the same issue on all of them. The query breaks off at different points on each sever - for example one of them breaks off at 209 records as i said above, another one at 307. The last record always gets truncated at the same place. The csv attachment size it's about 64 kb - which is well below the 4MB limit i've configured the database Maximum File Size bytes parameter.
What i am doing basically is creating a job that is meant to execute a stored procedure and send the results in a csv in an email. The stored procedure is something like:
select col1, col2, col3
from table A1
where col1 > 1000 order by col1
if I have the following code:
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.SendMailTask;
class TestSendMailTask
{
public static void Main()
{
Package pkg = new Package();
ConnectionManager smtpCM;
smtpCM = pkg.Connections.Add("SMTP");
smtpCM.Name = "SMTP Connection Manager";
smtpCM.ConnectionString = "smtphost";
Executable exe = pkg.Executables.Add("STOCKendMailTask");
TaskHost thSendMailTask = (TaskHost)exe;
{
thSendMailTask.Properties["SmtpConnection"].SetValue(thSendMailTask, "SMTP Connection Manager");
thSendMailTask.Properties["ToLine"].SetValue(thSendMailTask, "someone1@example.com");
thSendMailTask.Properties["CCLine"].SetValue(thSendMailTask, "someone2@example.com");
thSendMailTask.Properties["BCCLine"].SetValue(thSendMailTask, "someone3@example.com");
thSendMailTask.Properties["FromLine"].SetValue(thSendMailTask, "someone4@example.com");
thSendMailTask.Properties["Priority"].SetValue(thSendMailTask, MailPriority.Normal);
thSendMailTask.Properties["FileAttachments"].SetValue(thSendMailTask, "C:\test_image.jpg");
thSendMailTask.Properties["Subject"].SetValue(thSendMailTask, "Testing the SendMail Task");
thSendMailTask.Properties["MessageSourceType"].SetValue(thSendMailTask, SendMailMessageSourceType.DirectInput);
thSendMailTask.Properties["MessageSource"].SetValue(thSendMailTask, "This is only a test.");
}
DTSExecResult valResults = pkg.Validate(pkg.Connections, pkg.Variables, null, null);
if (valResults == DTSExecResult.Success)
{
pkg.Execute();
}
}
}
-------
How do I make it a workable package so it compiles , w/ javadoc style comments and instructions, so, other people can use it?
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
View 3 Replies View Related
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
Any option anyone knows will help.
Thanks.
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies View RelatedIn the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
oLogFile.WriteAllText("C:MyFile.txt", strValue)
oLogFile.WriteAllText("C:MyFile.txt", rsSQLOutput)
I went through this link that explains how to write XML Result Set into a File, But this doesn't help as it writes in XML format.
Would you please give me a hint of code how I can go upon.
In short, does the €œTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €œTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €œThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting.
Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container.
Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction.
Task failed: Copy BusinessContacts database table data 1
Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction.
Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction.
SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure.
The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
Hi
I have an application like fetching records from the DataBase(MS Access 2000) and results i have to use in Script Task. At present i have used the record fetching query,connection string in Script itself. I would like to use in Independently. Is there any Tools like (Control Flow Tools like Execute SQL Task) are there to fetch the result set from Acccess and can use the fetching results in Script Task....
Thanks & Regards
Deepu M.I
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
A.
I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.
Now I want to add a functionality :
If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop
if failure mark all tasks at enable and start extracting data and continue further with execution.
I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.
Hi everyone,
For first time I'm testing this task and surprisingly, when I try "Edit Package" option:
1)The DTS host failed to load or save the package properly
2)The selected package cannot be opened
3)Error HRESULT E_FAIL has been returned from a call to a COM component
But after these messages you can see all the tasks but they haven't name!!
It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.
I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?¿
Any guidance or idea about this?
1 :Control Flow Excute SQL task: Truncate Table
2: Dataflow Task: Datareader--Script componant--OLE DB Destination (SQL Server 2005--a single table --always around 600,000 rows)
How do I set up a transaction where if there is a failure the Truncate Table command will roll back---and the OLE Destination (A single SQL Server table) will be left the same as before the load started.
Another question with that volume of data --600,000 rows will a truncate table be pratical in a transaction
Any ides welcome
thanks in advance
David
HI, I need to trigger some packages upon existance of specific files in a particular directory. Sound lkike the file watcher task (from SQLIS) would do the job but I am wondering what is the difference of using this tool instead of a for each loop container. I mean, If a file exists in a directory, the for each loop container will detect it. Since the file watcher is not a service, the package containing it needs to ne scheduled on a regular basis for the filewatcher to detect the file, right? So, a for each loop container would do the job? So, waht wouldbe the advantage of using the file watcher task?
Thank you,
Ccote