I have a script task in my control flow that does some validation checking. If one of those checks fails, I want to fail the task and send an appropriate message via email. I've accomplished this by updating a package that is then used as the source for the email message.
View 12 RepliesOk, im making some progress. So what i have is a Message Queue Task which is bound to a message queue connection manager (which 'tests' ok). The Message Queue Task is set to recieve, variable from string message (declared a variable of type string) and to remove the message from the queue. The output of that task is piped into the data flow task.
The data flow task expands into a XML Source which is configured to get its input from the string i declared in the Message Queue Task and i point the schemas path to an appropriate schema. I then pipe the output of that into a SQL server destination which ive mapped all the columns from the XML message to a table (which the SQL server destination created for me).
It all looks good on paper, and builds properly with no errors etc. There is already a message in the appropriate private queue. When i go to debug it, it just sits on the Message Queue Task node (its yellow) and goes no further. No data is put into the DB. I have put a watcher on the link between the XML Source and the SQL server destination, and can see no data being piped through.
Even if i send another message, the execution of my package doesnt step passed the Message Queue Task. Its just sitting there waiting for something? what? I thought it would block until there was a message on that queue, and then process it if and when it arrives. But it doesnt seem to do that.
Any ideas??
I read on MSDN that you need integration services installed. I have checked and i do, and its running. Is theres something else i need to configure?
Help!
Simple Question.
I have a requirement to read XML Messages from a Remote private MSMQ.
These messages are essentially Database records that will need cleaning up and insterting into a Local Database table.
Is this possible with SSIS?
Would Biztalk be a more suitable tool for this type of process?
If its possible, are there any resources taht can point me in the right direction?
Thanks for your help!
J.
Hi All,
I want to show the error message during Data Flow In SSIS, if an error would occur. I am able to redirect the row in file but i want to display the error like "Error : Its Not Set".
Is it possible? if please help me.
I have a C# application that get data (i.e. select Firstname, LastName from person) to form a DataSet object (i.e. PersonName variable inside my code). Then I want to post this DataSet object into a local private queue (the path is: .privatemyTestQ). Note that I carefully labled the message to be "Variables Message" (as needed per anothre thread discussion here in this forum).
I created a receiving package, in which I want to use SSIS's Message Queue task to retreive the above DataSet object (from C# application). I got failure:
[Message Queue Task] Error: An error occurred with the following error message: "Root element is missing.".
However, As a comparison research, I created another SSIS sending-package. And I used ADO.NET provider to get the same data to store the sull result set in a package-level variable. Then I use Message Queue task to post this variable (i.e. object) to the same private queue above. Then I run my above receiving package. I was successful to read back the messge that I posted from SSIS sending package. (Please note that if I use OLEDB provider for sending package to get data from database, the MSMQ task for sending failed due to serializtion issue for __ComObject. With ADO.NET provider, the result set is represented as a type of DataSet).
I then curiously looked into message body from Computer Management Counsol. I found that message sent from SSIS is in SOAP format while message from my C# application is NOT in SOAP (but only in XML format). Obviously SSIS MSMQ task serialize objects into SOAP format.
Can anyone here please help on how to serialize my DataSet object from my C# app) in compliance with the MSMQ task's spec so that I can read message from Q using SSIS package.
I use Visual Studio 2005 and MSMQ 3.0 version.
Your help is appreciated.
Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.
View 4 Replies View RelatedHow To Use Message Queue Task In Integration Services
I have a strange situation in an Message queue task in SSIS.
I serialize an object in a C# application and add that to an MSMQ as a string. I also ensure that I set the label to "String Message" so that my Message Queue Task can actually receive the message as a String message to variable.
I created an SSIS package that has an Message Queue listener that feeds into a Script task inside a for-each loop.
For each message that I obtain, I invoke a script task that retrieves the value of the variable and then processes this information.
When I enter the entry into the MSMQ, it goes in perfectly fine (since I also tested retrieving this entry from a C# app). However when I use the same logic on the SSIS package using the Script task, I get junk chinese characters.
Has this happened to anyone else?
Any feedback would be great!
Anup
Here is the code for the script task:
mports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim statusMessage As String
statusMessage = CType(ReadVariable("ReviewerHealthXmlMessage"), String)
System.Windows.Forms.MessageBox.Show(statusMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
End Class
Hello,
I'm using the Message Queuing task to create a local private queue message. Everything works great on a 32 bit machine. When I try this on a 64 bit Itanium Cluster I keep getting the message "Message queue service is not available" in my SSIS log. I've using this string as my path "ClusterNameprivate$QueueName". Does anyone know of any issues with the Message Queue task on 64 bit or a cluster? The Message Queue service is up and running, it doesn't make sense.
Thanks,
Andy
I everyone,
I'm learning SSIS from a book but the most simple example is not working.
The "For each task" I assigned "xyz" as name.
I added a variable onto the "variables" window and called it "counter" Its scope was set to "xyz".
Inside the "for loop" ("xyz") editor i assigned values as follows:
"initexpression"-"@counter=0"
"eval expression"-"@counter<5"
"assign expression"-"@counter=@counter+1"
I added "Script task" onto the "For each" container and into the "Script task editor" I assigned
the following values:
"ReadOnlyVariables"-"counter"
Its design script is set to:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim variables As Variables
If Dts.Variables.Contains("Counter") = True Then
Dts.VariableDispenser.LockOneForRead("Counter", variables)
End If
MsgBox("You are in iteration: " & CStr(variables("Counter").Value))
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Runnig it i get the following error message:quote:
"DTS Script task has encountered an exeption in user code:
Project name:ScriptTask_e7d98dbad0de4041bcdc9079a5c2fa65
Object reference not set to an instance of an object.."
The line where the error occurs is from within the above script:
"MsgBox("You are in iteration:" " & CStr(variables("Counter").value))
Anyone understands what that means? What is "object reference" and how do i set it to an instance of an object?
Thanks
I'm trying to use the message queue task in SSIS 2005 and not getting very far. Right off the bat I'm trying to create a Message Queue Connection Manager and when I enter the path, trying both "seawxxxx estq" (my local computer name) or ". estq" and test it, it comes back with "invalid path". Have done quite a bit of searching around and can't find anything on this particular error.
Suggestions? Thanks in advance.
i have developed a pakage which populates a two different tables with reference to the xml files added to a folder which is watched by a security WMI task.it is governed by a sequence container which contains three for each loop container for working on the different files.i have different event handlers set up inside for each loop container tasks which contains , data flow task, execute sql task, and moving the processed file to the desired destination.i want to set up a send mail task on the package level using event handler on error, where i have set up a task for looging the error to the error table , i have tried to collect all the error messages in a array list variable . and trying to use that variable a s a message source. i could not under stand if i set the propogation variable in the sequence container as false than will the onpost execute event will fire the onpostexecute event handler in the package level.if show how can i send only one email for all the errors of package with error looging.
View 6 Replies View RelatedI everyone,
I'm learning SSIS from a book but the most simple example is not working.
The "For Loop task" I assigned "xyz" as name.
I added a variable onto the "variables" window and called it "counter" Its scope was set to "xyz".
Inside the "for loop" ("xyz") editor i assigned values as follows:
"initexpression"-"@counter=0"
"eval expression"-"@counter<5"
"assign expression"-"@counter=@counter+1"
I added "Script task" onto the "For each" container and into the "Script task editor" I assigned
the following values:
"ReadOnlyVariables"-"counter"
Its design script is set to:[code]
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim variables As Variables
If Dts.Variables.Contains("Counter") = True Then
Dts.VariableDispenser.LockOneForRead("Counter", variables)
End If
MsgBox("You are in iteration: " & CStr(variables("Counter").Value))
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
[/code]
Runnig it i get the following error message:
DTS Script task has encountered an exeption in user code:
Project namecriptTask_e7d98dbad0de4041bcdc9079a5c2fa65
Object reference not set to an instance of an object..
The line where the error occurs is from within the above script:
[code]
"MsgBox("You are in iteration:" " & CStr(variables("Counter").value))[/code]
Anyone understands what that means? What is "object reference" and how do i set it to an instance of an object?
Thanks
while i was trying to execute the code for Sending an "HTML Mail Message with the Script Task" given at the link below http://msdn2.microsoft.com/en-us/library/ms403365.aspx
following error was encountered.
DTS Script Task has encountered an exception in user code:
Project namecriptTask_098956444e9f4ae195c3565569c9444b
The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_098956444e9f4ae195c3565569c9444b.ScriptMain.Main() in dts://Scripts/ScriptTask_098956444e9f4ae195c3565569c9444b/ScriptMain:line 29
please help me
hi
I am trying something very simple but cannot see why it is failing.
I am trying to add a filename to the message source of a mail task from a variable.
I have Variable 1 User::CurrentFilePath, Scope = ForEachLoop, Data Type = String
and Variable 2 User::mailSource, Scope = ForEachLoop, Data Type = String
Variable 2 EvaluateAsExpression is set to True and the Expression for now is simply "Processing of File " + [User::CurrentFilePath] + "has Begun"
However when i enter this i get
The expression for variable "Test2" failed evaluation. There was an error in the expression.
In the Value and Value type boxes of the variable property.
Any advice greatly received
Regards
Scott
I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub
Exec Prcoess task with source : ftp
destination :ftpunzip
work directory ftpunzip
executable : c:Program FilesWinZip
i am using expressing.
It is doing the unzip but getting this error
package process on server server1 has failed within the Task 'Unzip Files' with the following errors:
>
> File/Process "WZUNZIP.EXE" does not exist in directory "c:Program FilesWinZip".
This is the error message i am getting it
I had the SP, I want to call in Script Task , had the Result set data value then I need pop up message box. So how can I call stored procedure result in message box in ssis script task using C#.
and I want to use SSIS -OLEDB connection.
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
System.Data.SqlClient.SqlCommand sqlComm;
cm = Dts.Connections["OLE_TEST_"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
sqlComm = new System.Data.SqlClient.SqlCommand("Exec dbo.sOp_xx_XXXe_VXX 280", sqlConn);
sqlComm.ExecuteNonQuery();
above code , no message box.
This is the first time I've used SQL Server Integration Services, and I have it installed with SP2 applied on my local machine. I have created a Package, and dropped a Message Queue Task on it. I have pointed the Message Queue Connection Manager at a private queue on my machine. I have set the "Message" property to "Send message", I have changed the message type to "String Message" and entered "Test string" into the StringMessage property. When I execute the package (by clicking the "Start debugging" button on the toolbar), I get the following error message in the Execution Results:
[Message Queue Task] Error: An error occurred with the following error message: "The ServicedComponent being invoked is not correctly configured (Use regsvcs to re-register).".
There is no more diagnostic data that I can see in the Execution Results. Can anyone please explain to me how I can get this working?
Thanks!
Bryan
I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".
Task failed: Bulk Insert Task
In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');
What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}
Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul
Hi -
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
Where do I change the security settings?
Thanks - Grace
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?>
<ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template name="identity" match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="kw">
<xsl:copy>
<xsl:apply-templates select="@*"/>
<xsl:attribute name="ihs_cats_seq" select="position()"/>
<xsl:apply-templates select="node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
Its 5th line is the first xsl:template element.
What is going on here? I do not see multiple root elements in either the XML document or the XSLT stylesheet.
Thanks!
In my SQL Server, I see the below message in the Application Event Viewer
"18265 :
Log backed up: Database: HSD, creation date(time): 2007/01/06(05:05:05), first LSN: 1439:495055:1, last LSN: 1439:496675:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:MSSQLBACKUPHSDHSD_tlog_200707141300.TRN'})."
When I save the application event viewer and open it in another server, I do not see the above message, instead I get the following message:
" Can't find or load message DLL. Message DLL must be in path or in current directory."
Any thoughts to overcome this problem is appreciated.
Thanks
Santhosh
I run SB between 2 SQL servers. In profiler on an initiator side I see next error: 'This message could not be delivered because its message timestamp has expired or is invalid'. For the conversation we use best practice, i.e. target closes a conversation. Target side succeed to close conversation, but initiator still stay in DO (disconnect_outbound).
What is a reasone for the error? What to do?
I see in profiler this error: "This message could not be delivered because its message timestamp has expired or is invalid"
What is a reason for error?
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
Hello,
I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.
// Create an empty request message
string Msg = "Hello";
MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));
Message request = new Message("Request", body);
Thanks,
Scott Allison...
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.