Using Message Queue Task And Serialization
Mar 7, 2007
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.
View 2 Replies
ADVERTISEMENT
Apr 18, 2006
Ok, 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!
View 15 Replies
View Related
Aug 2, 2007
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.
View 1 Replies
View Related
Jan 17, 2007
How To Use Message Queue Task In Integration Services
View 1 Replies
View Related
Mar 12, 2008
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
View 1 Replies
View Related
Feb 2, 2006
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
View 1 Replies
View Related
Nov 17, 2007
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.
View 5 Replies
View Related
Jan 15, 2008
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
View 12 Replies
View Related
Jun 12, 2007
Is it possible to modify the sequence of messages in a SSB queue? Or to create messages so that their sequence is based on a value in the message rather than the order in which they were sent to the queue?
I am trying to determine if SSB will help me solve a situation where BizTalk needs to process a prescribed sequence of messages that may not be received in the correct order. (i.e. I need to resequence the messages). Each message contains a field with the sequence number and also a field identifying the total number of messages in the sequence.
I realise that the Sequential Convoy Aggregrator EAI pattern can potentially provide a solution to this, or even the Sequence Guards pattern by McGeeky, but I guess I was hoping that SSB might provide a slightly more efficient solution(?)
Thanks,
Dan
View 2 Replies
View Related
Jun 16, 2007
Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.
I was testing this scenario:
try to send messages on a disabled queue and log the problem.
But the transmission_status from the trasmission_queue is always empty.
This is the code that I tried to execute between the send and the commit and after the commit:
WHILE (1=1)
BEGIN
BEGIN DIALOG CONVERSATION .....
SEND ON CONVERSATION ......
if select count(*) from sys.transmission_queue <> 0
BEGIN
set @transmission_status = (select transmission_status from sys.transmission_queue where conversation_handle=@dialog_handle);
if @transmission_status = ''
--Successful send - Exit the LOOP
BEGIN
UPDATE Mytable set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
ELSE
raiserror(@transmission_status,1,1) with log;
END
ELSE
BEGIN
UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
END
COMMIT TRANSACTION;
As I wrote before the @transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!
Maybe what I'm trying to reach has no sense?
With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?
Thank you!Marina B
View 3 Replies
View Related
Oct 2, 2006
I need to determine the actual date/time that a message was placed on the queue. In my "activated" procedure I want to log this information and pass it along to further processing routines. From what I can tell, the Queue table itself does not have this information captured.
View 4 Replies
View Related
Sep 1, 2006
Hi Folks,
I've found a pretty good code example on http://www.dotnetfun.com for a Asynchronous Trigger.
I've parsed through the Code, to understand how to wirte my own Async Trigger with a Service Broker, but the Code isn't working! It seems that the stored procedure don't receive the messages in the queue, but the queue get's filled.
MessageType
CREATE MESSAGE TYPE myMsgXML
VALIDATION = WELL_FORMED_XML;
Contract
CREATE CONTRACT myContractANY
(myMsgXML SENT BY ANY)
Queue
CREATE QUEUE myQueue
WITH STATUS = ON,
RETENTION = ON,
ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = sp_myServiceProgram,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF
)
Service
CREATE SERVICE myService ON QUEUE myQueue (myContractANY)
Procedure (greped from http://www.dotnetfun.com/)
CREATE PROC sp_myServiceProgram
AS
-- This procedure will get triggered automatically
-- when a message arrives at the
-- Let's retrieve any messages sent to us here:
DECLARE @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ID INT,
@COL2 VARCHAR(MAX);
DECLARE @Queue TABLE (
MessageBody VARBINARY(MAX),
MessageTypeName SYSNAME);
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @Queue
), TIMEOUT 5000;
-- If no messages exist, then break out of the loop:
IF NOT EXISTS(SELECT * FROM @Queue) BREAK;
DECLARE c_Test CURSOR FAST_FORWARD
FOR SELECT * FROM @Queue;
OPEN c_Test;
FETCH NEXT FROM c_Test
INTO @MessageBody, @MessageTypeName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Let's only deal with messages of Message Type
-- myMsgXML:
IF @MessageTypeName = 'myMsgXML'
BEGIN
SET @XML = CAST(@MessageBody AS XML);
-- Now let's save the XML records into the
-- historical table:
INSERT INTO tblDotNetFunTriggerTestHistory
SELECT tbl.rows.value('@ID', 'INT') AS ID,
tbl.rows.value('@COL2', 'VARCHAR(MAX)') AS COL2,
GETDATE() AS UPDATED
FROM @XML.nodes('/inserted') tbl(rows);
END
FETCH NEXT FROM c_Test
INTO @MessageBody, @MessageTypeName;
END
CLOSE c_Test;
DEALLOCATE c_Test;
-- Purge the temporary in-proc table:
DELETE FROM @Queue;
END
Send Message in a Update Trigger
SELECT @XML = (SELECT * FROM inserted FOR XML AUTO);
-- Send the XML records to the Service Broker queue:
DECLARE @DialogHandle UNIQUEIDENTIFIER,
@ConversationID UNIQUEIDENTIFIER;
/*
The target Service Broker service is the same
service as the initiating service; however, you
can set up this type of trigger to send messages
to a remote server or another database.
*/
BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE myService
TO SERVICE 'myService'
ON CONTRACT myContractANY;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE myMsgXML
(@XML);
-- Let's detect an error state for this dialog
-- and rollback the entire transaction if one is
-- detected:
IF EXISTS(SELECT * FROM sys.conversation_endpoints
WHERE conversation_handle = @DialogHandle
AND state = 'ER')
RAISERROR('Dialog in error state.', 18, 127);
ELSE
BEGIN
--I want to list the queue after the trigger so I disabled
--END CONVERSATION @DialogHandle;
COMMIT TRAN;
END
The Problem is, that the Procedure doesn't even get started! So I tried to receive the Queues manually
WAITFOR (
RECEIVE message_body, message_type_name
FROM myQueue INTO @Queue
), TIMEOUT 5000;
and I run always into the timeout and get nothing back. A Select * FROM myQueue gives me some results back. Why I can't recevie?
Would be grateful for help, or at least a good tutorial, I haven't found one yet....
thx and greez
Karsten
View 1 Replies
View Related
Jun 2, 2006
I am implementing a message queue system in SQL Server 2000. My queue table looks something like this:
[MessageId] [uniqueidentifier] NOT NULL,
[MessageType] [uniqueidentifier] NOT NULL,
[Status] [tinyint] NOT NULL,
[SubmittedTime] [datetime] NOT NULL,
[StartTime] [datetime] NOT NULL,
[DispatchedTime] [datetime] NULL,
[CompletedTime] [datetime] NULL,
[MessageData] [image] NULL
This is how I retrieve the next message for processing:
SELECT TOP 1 *
FROM [Queue].[MessageQueue] WITH (ROWLOCK, UPDLOCK, READPAST)
WHERE [StartTime]=@pStartTime AND [MessageType]=@pMessageType AND [Status]=@pStatus
ORDER BY [StartTime]
and mark it as being processed:
UPDATE [Queue].[MessageQueue] SET [Status]=1 WHERE [MessageId]=@pMessageId
After message has been processed I delete it from the queue:
DELETE FROM [Queue].[MessageQueue] WHERE [MessageId]=@pMessageId
All database accesses are transactional with default READ COMMITTED. The problems start when there are a few concurrent accesses: I get deadlocks when retrieving next message. If I do not delete message after processing then there is no deadlock. But this is not what I need.
I played with different isolation levels and locking hints and was able to avoid deadlock using TABLOCKX:
SELECT .... FROM [Queue].[MessageQueue] WITH (TABLOCKX)
But in this case you cannot concurrently retrieve messages which was my goal from the beginning. How do I achieve this?
Thank you,
Alex
View 18 Replies
View Related
Aug 14, 2007
Hi,
I am using distributed transactions where in I start a TransactionScope in BLL and receive data from service broker queue in DAL, perform various actions in BLL and DAL and if everything is ok call TransactionScope.Commit().
I have a problem where in if i run multiple instances of the same app ( each app creates one thread ), the threads pop out the same message and I get a deadlock upon commit.
My dequeue SP is as follows:
CREATE PROC [dbo].[queue_dequeue]
@entryId int OUTPUT
AS
BEGIN
DECLARE @conversationHandle UNIQUEIDENTIFIER;
DECLARE @messageTypeName SYSNAME;
DECLARE @conversationGroupId UNIQUEIDENTIFIER;
GET CONVERSATION GROUP @conversationGroupId FROM ProcessingQueue;
if (@conversationGroupId is not null)
BEGIN
RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue WHERE conversation_group_id=@conversationGroupId
END
if @messageTypeName in
(
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
)
begin
end conversation @conversationHandle;
end
END
Can anyone explain to me why the threads are able to pop the same message ? I thought service broker made sure this cannot happen?
View 11 Replies
View Related
Jun 15, 2007
Hello,
I have almost finished to design my Service Broker application and I found out something strange.
I was tring to handle the Target queue disabled scenario, because I want to save to message that was not sent so I can create an alert to the user to say that some messages as not been sent.
To disable to queue I run:
Alter queue [dbo].[Receivedqueue] with status = off
I had the Event Viewer in front of me and I have seen the suddenly it appeared a lot of this informational events:
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 15/06/2007
Time: 15:00:47
Description:
The activated proc [dbo].[OnReceived] running on queue TestReceiver.dbo.Receivedqueue output the following: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
What this message means?
I would like to avoid to have it because it appears a lot of time and I don't want to fill up my Event Viewer!!
I don't know of it is important but in the moment I executed the code there was not CONVERSING conversation and all the queues were empty.
Somebody has seen this error before or I have to send to the forum the [dbo].[OnReceived] code as well?
thankx
Marina B.
View 3 Replies
View Related
Oct 6, 2006
I made a point type by C# for deploying in SQL2005.
It builds successfully every time.
When I deploy, it gives the following error:
"Error: Type "SqlServerProject1.TypePoint" is marked for native serialization, but field "x" of type "SqlServerProject1.TypePoint" is not valid for native serialization." although x variable is value type decimal.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedType(Format.Native, IsByteOrdered=true)]
public struct TypePoint : INullable
{
private bool m_Null;
private Decimal x;
private Decimal y;
public override string ToString()
{
// Replace the following code with your code
if (IsNull) // added part
{
return "null";
}
else
{
return x + ":" + y;
} // end of this part
// return "";
}
public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}
public static TypePoint Null
{
get
{
TypePoint h = new TypePoint();
h.m_Null = true;
return h;
}
}
public static TypePoint Parse(SqlString s) // Most Important Part
{
if (s.IsNull)
{
return Null;
}
else // added part
{
TypePoint p = new TypePoint();
string str = s.ToString();
string[] xy = str.Split(':');
p.x = Decimal.Parse(xy[0]);
p.y = Decimal.Parse(xy[1]);
return p;
}
}
public Decimal X
{
get
{
return x;
}
set
{
x = value;
}
}
public Decimal Y
{
get
{
return y;
}
set
{
y = value;
}
} // end of added part
// This is a place-holder method
public string Method1()
{
//Insert method code here
return "Hello";
}
// This is a place-holder static method
public static SqlString Method2()
{
//Insert method code here
return new SqlString("Hello");
}
// This is a place-holder field member
public int var1;
}
View 4 Replies
View Related
Jan 11, 2006
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
View 5 Replies
View Related
Jun 22, 2005
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 Replies
View Related
Apr 13, 2008
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.
View 7 Replies
View Related
Sep 11, 2006
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 Related
Apr 23, 2008
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
View 4 Replies
View Related
May 2, 2008
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 Related
Apr 23, 2008
I 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
View 22 Replies
View Related
Mar 28, 2008
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
View 7 Replies
View Related
Jun 13, 2007
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
View 1 Replies
View Related
Mar 9, 2007
Hello,I am trying to serialize (binary) a class and save it in a database. I followed a few examples I found in internet and this is what I came up with: 1 ' Rows
2 <Serializable()> _
3 Public Class Rows
4 Implements ISerializable
5
6 Private _Rows As New Generic.List(Of Row)
7 Public Property Rows() As Generic.List(Of Row)
8 Get
9 Return _Rows
10 End Get
11 Set(ByVal value As Generic.List(Of Row))
12 _Rows = value
13 End Set
14 End Property ' Rows
15
16 ' New
17 Public Sub New()
18 End Sub ' New
19
20 ' New
21 Public Sub New(ByVal siRows As SerializationInfo, ByVal scRows As StreamingContext)
22 End Sub ' New
23
24 ' GetObjectData
25 Public Sub GetObjectData(ByVal siRows As SerializationInfo, ByVal scRows As StreamingContext) Implements ISerializable.GetObjectData
26
27 siRows.AddValue("Rows", Me.Rows)
28
29 End Sub ' GetObjectData
30
31 Public Sub Serialize(ByVal filename As String)
32
33 Dim sRows As Stream = Stream.Null
34 Try
35 sRows = File.Open(filename, FileMode.Create, FileAccess.ReadWrite)
36 Dim bfRows As New BinaryFormatter
37 bfRows.Serialize(sRows, Me)
38 Finally
39 sRows.Close()
40 End Try
41
42 End Sub ' Serialize
43
44 Public Shared Function Deserialize(ByVal filename As String) As Rows
45
46 Dim sRows As Stream = Stream.Null
47 Try
48 sRows = File.Open(filename, FileMode.Open, FileAccess.Read)
49 Dim bfRows As New BinaryFormatter
50 Return CType(bfRows.Deserialize(sRows), Rows)
51 Finally
52 sRows.Close()
53 End Try
54
55 End Function ' Deserialize
56
57 End Class ' Rows
After serializing the class I need to save it in an a SQL 2005 database.But all the examples I followed use the filename ... Anyway, do I need to do something to save this into an SQL 2005 database or can I use it as follows? And how can I use this?This is the first time I do something like this so I am a little bit confused.Thanks,Miguel
View 1 Replies
View Related
Feb 9, 2008
Hi,
I have designed a Contacts application, where I need to persist contacts to a file.
The contacts can exceed over 10,000
Which will be a better option to persist contacts to - A XML file OR SQL DataBase ?
Thanks
View 1 Replies
View Related
Oct 3, 2006
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
View 3 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 11 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 1 Replies
View Related
Mar 7, 2007
Jahnavi writes "I have some logic for Encryption and Decryption following the Serialization and Deserialization of an object.
I would like to move this approach to CLR stored procedures to leverage the advantages in SQL 2005.
Could you please provide me with the details of the pros and cons of the approach and the various possibilities over this.
Would be glad to receive any kind of inputs from you.
Thanks,
Jahnavi"
View 1 Replies
View Related
Jul 4, 2007
I have created a UDT Point from an MSDN example. Its declaration is the following:
Code Snippet [Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
IsByteOrdered = true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
private bool is_Null;
private Int32 _x;
private Int32 _y;
...
So it means that this class will be serialized in the native SQL Server format.
Is there any possibility to deserialize data retrieved from server as bytes to an object instance?
For example:
Code Snippet SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Retrieve the raw bytes into a byte array
byte[] buffer = new byte[32];
long byteCount = rdr.GetBytes(1, 0, buffer, 0, 32);
Point pt = new Point();
// Here I need to fill the created object with retrieved data
}
After I retrieved the data in bytes format, I need to write it to the object. How can I do this? In fact, I have no possibility to use SqlDataReader in the following manner:
Code Snippet // Retrieve the value of the UDT
Point pnt = (Point)rdr[1];
// Point pnt = (Point)rdr.GetSqlValue(1);
// Point pnt = (Point)rdr.GetValue(1);
because I retrieve data in bytes not from the server, but from an unmanaged application.
I have a possibility to create an instance of the class, but how can I fill this instance with data? Is it possible to do using native SQL Server native format? How can I do this when UDT uses user defined serialization?
View 3 Replies
View Related
Jun 17, 2015
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
View 6 Replies
View Related
