Message Output From XSLT XML Task

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


ADVERTISEMENT

XML Task &&amp; XSLT

Feb 23, 2007

Can I use the XML task in SSIS to create an excel document? If so which operation type is best to do it with, XSLT?

I'm trying create excel files dynamically with nothing more than a SQL statement that I'm passing as a variable which generates XML. I would then like to take that variable and combine it with a template and create an excel document. Any help would be appreciated. I do not want to use the data flow because it requires all transformations ahead of time.



Thanks,

Phil

View 3 Replies View Related

XSLT Transformation In XML Task

Sep 10, 2007

Hi,

I've a xml file and I want to substitute some of the values from database in this xml using XSLT transformation making use of XML task. How can this be achieved?

Thanks

View 5 Replies View Related

XML Task: How To Pass Arguments To XSLT

Aug 10, 2006

In the XML Task if you set the OperationType to XSLT, how do you pass arguments to the Transform like you would in .Net by using the XsltArgumentList class? Thanks.

View 3 Replies View Related

Xslt Filters On Xml Output. Cdata-section-elements And Omit-xml-declaration Problems.

Sep 14, 2006

Hi All,



I'm using some xslt documents to transform the xml output of my Reports
but have come across two curiosities where the xslt filter seems to
behave unusually.



Firstly, I need the final saved file to have an xml declaration, which
I believe it should do by default. Even if I put
omit-xml-declaration="no" in the xsl:output tag I don't get an xml
declaration. At present we have a custom job that writes these
declarations back into the xml after SRS has saved it.



Secondly and more importantly, I need to have some of my output tags
wrapped in CDATA sections. I've tried using the cdata-section-elements
attribute, again with no luck.

my XSLT looks something like this (simplified for space)



<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" encoding="utf-8" media-type="text/xml" omit-xml-declaration="no" cdata-section-elements="description"/>

<xsl:template match="/">

<xsl:for-each select="Report/table1/Detail_Collection/Detail">

<item>

<description>


<xsl:value-of select="@Description"/>

</description>

</item>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>



The output is something like:



<item>

<description>My first description text...</description>

</item>


<item>


<description>My seconddescription text...</description>


</item>



What I want is:

<?xml version="1.0" encoding="utf-8"?>

<item>


<description><![CDATA[My first description text...]]></description>


</item>



<item>



<description><![CDATA[My secondfirst description text...]]></description>



</item>



All help gratefully appreciated.



Thanks - Andrew.

View 5 Replies View Related

Strip DTD Out; Help With Some XSLT Inside Of SSIS XML Task

Sep 4, 2007

I'm reading over this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884062&SiteID=1

and I'm kinda lost as to what to do to strip out the dtd from an XML file I am downloading. I do NOT know XSLT and for that reason, I can't follow his logic.

My SSIS package downloads my XML file just fine, now I need to do a strip of the DTD line in my XML Task.

The person who provided the solution in the above post said to do this...





Code Snippet
Operation Type: XSLT
Source Type: Variable
Source: Variable's name containing the xml text
Save Operation Result: True
DestinationType: Variable
OverwriteDestination: True
Destination: Variable's name which is to contain the original xml minus the DTD.
SecondOperandType: Variable







That stuff I understood. I'll replace variables with my files because they are stored that way, but from what I can tell, that's not my problem.

The stuff he says below this comment is going over my head like a ton of bricks. I can't figure out how to do it.

This is the kind of line of my XML that I want to strip out.

https://www.myaddy.com/pbdr.dtd"[]>

and then he said this...





Code Snippet
Since XSL doesn't know about DTDs, telling it to copy everything strips out DTDs. Then use the Variable specified in the Xml task's SecondOperand as the Source data for the xml source.


Code Snippet

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<xsl:copy-of select="." />

</< FONT>xsl:template>

</< FONT>xsl:stylesheet>

A note on how to paste a multi-line xml document into a Integration Services String variable:


Integration Services String variables textboxes are not multi-line, in the Windows sense of a line (CR+LF),

So, in order to paste multi-line text (which xml docs almost always are), save a temporary copy with a unix line ending.

That is, create an xml file in visual studio, and paste your sample original xml in there. Go to File/Advanced Save options, and save the xml with the the settings of Encoding: Unicode (utf-8 without signature) - CodePage 65001, and most importantly, set the Line endings dropdown to "Unix (LF)".

After selecting "OK", copy and paste the text from Visual Studio's xml file editor into the IS variable, and you'll note all the xml data appears.





So can anyone walk me through a dummies version of what he is suggesting to do?

Thanks,
Keith

View 1 Replies View Related

Execute SQL Task – Output Parameter On Stored Procedure Causes Task To Fail.

Dec 2, 2005

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 Related

Add Error Output To Email Message

Feb 7, 2008

Hello,
Is there a way that I can set up an error handler on a DataFlow so that errant rows within the DataFlow are delivered to a user via email?

I'm looking for Primary Key Violations. In this case, there are two columns of data involved in the DataFlow (AcctCode, RebateRate).

Thank you for your help!

cdun2

View 11 Replies View Related

Transact SQL :: How To Set Error Message To Output Parameter

Aug 31, 2015

In Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?

create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY

[code].....

View 4 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

T-SQL (SS2K8) :: How To Catch Output Message On Backup Successful Completion / Failure

May 20, 2014

The problem is as follows. SQL Express 2008 R2, backup is made by the means of bat file and sp. Need to have a table that would hold columns like 'date_of_backup', 'duration', 'message' (the one that appears on 'messages' tab in the result area after backup completion/failure). This is an example of real message that I want to catch:

Server: Msg 911, Level 16, State 11, Line 1
Database 'not_existing_db' does not exist. Make sure that the name is entered correctly.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

View 0 Replies View Related

Message Queue Task

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

Set Log Message Within Script Task

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

Message Queue Task

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

Error Message In DataFlow Task

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

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 View Related

How To Use Message Queue Task In Integration Services

Jan 17, 2007

How To Use Message Queue Task In Integration Services

View 1 Replies View Related

Junk Characters In Message Queue Task

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

Message Queue Task 64 Bit Cluster Issue

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

Error Message: Script Task, For Loop Container

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

Message Queue Task - Invalid Path Problem

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

Sending A Alert Message Using Send Mail Task

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

For Loop Container, Script Task: An Error Message

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

Sending An HTML Mail Message With The Script Task

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

Output Of Scheduled Task

Nov 1, 2000

I think I have a basic question. Can someone tell me
the command I can use to have a stored procedure run
and generate output? This is for SQL 6.5.

I would like to schedule the sp_help_revdatabase
stored procedure to run periodically and output it
to a file. I think this is possible, but I haven't
been able to come up with the syntax...

Thanks for any suggestions!
Toni

View 1 Replies View Related

XML Task And Variable Output?

Feb 20, 2007

Hello,



What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?





Thanks,

Phil

View 5 Replies View Related

Execute SQL Task W/ XML Output

Apr 19, 2007

I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?

View 4 Replies View Related

Using XML Output From UDF In Execute SQL Task

Feb 1, 2007

Hi,

I am trying to do the following without success;

I have a UDF which returns a string of XML data (of the data type XML)

I want to use this UDF within an Execute SQL Task to set a variable to the output of the UDF.  I then want to use the variable to pass the XML string to a XML Task to apply an XSLT to the XML.

I have set the following properties in the Execute SQL Task:

ResultSet: XML

SQLSourceType: Direct Input

SQLStatement: SELECT [dbo].[fn_GENERATE_XML](123) as rawxml

ResultName: 0

Variable Name:user::xml (where the variable is of type string)



My problem is something to do with the UDF call as if I substitute the UDF for a simple SELECT statement with a FOR XML Clause it works fine.

Any ideas where I am going wrong?

Thanks

 

 

View 1 Replies View Related

Send Mail Task Message Source Type Variable

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

Excel Task Has No Output Columns

Oct 20, 2006

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

View 3 Replies View Related

Execute A Task - How Can I See The Output At Different Stages ?

May 10, 2007

If ive got different pieces in a dataflow, how could I for example, see the output of a datasource before it gets to destination ? If im executing a task, what can i get access to from the immedate window to see the outputs at various stages ?

View 1 Replies View Related

Trouble Saving XML Output From SQL Task

Sep 7, 2007

I am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task.
Here is the stored procedure
Create Procedure [dbo].[HRGCSpace]
(@HRGSpace nvarchar(max) = ''Output)
as
Begin
Set NOCOUNT On
set @HRGSpace = (select
CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id',
WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id',
FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id',
SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id',
StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id'
from HRGCvCorrigoUnits
order by BuildingName, SpaceName
For XML Path ('Space'), Root('HRGSpaces'))
End
--------------------------------------
Here is the execute from the SQL Task
DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output

If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0
, then use in the SQLTask
General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False,
Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0
the Progress is
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

If I change it so that ResultSet = XML, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."

Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".

Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with
"[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!



View 4 Replies View Related

Sending An HTML Mail Message With The Script Task DOESNT WORK 4 ME

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







Copyrights 2005-15 www.BigResource.com, All rights reserved