Hi guys
Is there anyone who was able to successfully retrieve a full result set? I'm really having troubles getting the result after executing my query. Its really even hard to get sample codes over the net.
Please help guys.
Thanks in advance.
kix
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?
View 23 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.
Howdy all,
I have an Execute SQL Task that may return a result set. If it returns a result set, I'd like to log a failure in my package with the results visible.
I have logging turned on and that's working great. I've read about assigning results to a user variable of type Object and that's great. I can shred my results, thanks Jamie, with a Foreach loop no problem. Within that loop, I've got some VB that manipulates the values and will call Dts.Events.FireError as appropriate. However, VB is frowned upon here so my boss has asked that I push the VB logic into a Control Flow item.
I've built custom components already so I've got some familiarity with the process. Where I'm stuck at is figuring out _what_ the actual object type is in my code. The Connection manager is Native OLE DBSQL Native client. My Execute SQL Task uses a connection type of OLE DB with a Full result set. Results are stored in a variable named ErrorResultSet. Within the Execute method, I currently have this code set up in an attempt to pick apart the object and discover the available methods.
Code Block
Variables _variableCollection = null;
if (variableDispenser.Contains("ErrorResultSet"))
{
variableDispenser.LockForRead("ErrorResultSet");
}
variableDispenser.GetVariables(ref _variableCollection);
// Iterate through the variables that we were
// able to lock. Assigning values to entities as
// available.
foreach (Variable _en in _variableCollection)
{
switch (_en.Name)
{
case "ErrorResultSet":
Object _rs = _en.Value;
System.Type _type;
_type = _rs.GetType();
System.Data.DataSet _realResults;
_realResults = _rs as System.Data.DataSet;
// My expectation is that the cast of _realResults would
// not fail.
break;
}
}
// unlock before we go
_variableCollection.Unlock();
return DTSExecResult.Success;
At this point, my assumption is that the unboxed type of the recordset is not in the System.Data.DataSet inheritance chain as the cast failed. Anyone have insight into what it is? I can't seem to get any hits on google for what it's using behind the scenes in the Foreach ADO Enumerator.
Beyond the immediate question, anyone have thoughts on how else I can solve the problem? I had thought perhaps the task could raise an event if it returned rows but it didn't seem to have that functionality. Even if that had worked, telling the logging provider to capture the result set into the log might have been too much for native functionality. Another option I was thinking about would be to continue using the Enumerator and my custom component is a pure rewrite of the current Script task with the obvious downside being that I'd lose the generic-ness I was hoping to get with being able to hit my dataset.
This is the first time I've tried creating an "execute sql task" with a "full result set".
I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.
[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anyone else had success with a full result set?
Thanks,
-Lori
Currently, I'm developing an ETL program using Microsoft DTS on SQL Server 2000.
I've completed unit testing & component testing for all inner packages containing various types of executions - SQL task, ActiveX Script task, Error Handler, Transform Data task, Data Driven Query & Dynamic Properties task.
When unit & component tested, all inner package properties-"Limit the maximum number of tasks executed in parallel" is defaulted to 4.
However, the integration testing is failing because I've yet to successfully execute any of the Package tasks in the outer package, which runs & controls all inner packages from the outer package.
The error message encountered was:
---------------------------------------------------------------
| Error source: microsoft dts |
| Error description: execution was cancelled by another user. |
---------------------------------------------------------------
Please explain what might be the cause to this problem when I'm executing the outer package?
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
On an Execute Process Task, are there properties I can examine to determine the outcome of a process? I know that I can use a Success/Failure constraint to direct my workflow appropriately, depending on the outcome, but I would prefer the workflow continue on to the same task regardless, and then be able to change the flow later on based on the outcome of the process.
I see there is an ExecValueVariable property, and this may be what I'm looking for, but I have no idea what this is and can not find any documentation on it. I tried using it, but kept getting a runtime error trying to set it to a variable.
I know that a process returns a numeric value, and it would be nice if I could store this somewhere.
Any suggestions?
Thanks in advance.
Jerad
I am getting the following error when I execute my sql task:
An error occurred while assigning a value to variable "NullVar": " NO result rowset is associated with the execution of this query. "
I am executing a SP that has one input & one output parameter. The output parameter is returning a single row for debugging if the sp failes.
I tried using Jamie's method:(http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx) to get it to work but keep getting the above error. I have the following variables:
sqlSource (string) := Exec RBCprcsInsertWmsInvTransactionRecords '" + (DT_WSTR,10 ) @[User::SnapShotDate] + "', NULL"
NullVar (string)
In the execute sql task, I set the ResultSet to single row. I set SQLSourceType = variable & sourcevariable = user::SqlSource. In the result tab, I added a result set, NewResultName with the variable user::NullVar. I tried different configurations with the parameter mappings but nothing seemed to work. I didn't know if i still had to use this if I am using the sqlSource variable to drive the task.
So I am not sure what I am missing here. Anyone have any suggestions?
Thanks!
John
Hi,
Let's say that the query in my SQL Task returns a single integer number.
How can I put that single number in a variable?
Thank you.
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!
SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
Hi Guys,
I wonder if you can help with the following requirement.
I want to be able to conditionally execute an 'execute process task' depending on the result of a query. I have a table which I will select one record/row from upon each execution, this record has a char 1 'type' field which is the indicator for what process to then execute.
This should be quite a simple package and will be run every 60 seconds so needs to be as efficient as possible.
I am thinking I should go along the lines of using an Execute SQL task to select my row in to a result set, and using a series of precedence expressions to determine what process to execute. But im not really sure how.....
I am a newbie to SSIS and 2005 in general so would appreciate any help you can provide
Chris
Hi everybody,
For our customer we are trying to create dynamically local cube files. Because the requirements are complex and we need to generate a lot of cube files, we can't use the MDX script CREATE GLOBA CUBE to create the local cubes and we have to use SSIS to have it done automatically. These are the steps we are following:
1. In a SSIS package, through a Script task, we generate the ASSL script in order to create the database and we stores the script in a column of the XML datatype in SQL Server through a stored procedure "SSAS_TEST.InsertASSLScript". The following code is use in the script task:
Code Block
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports System.Xml
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Class ScriptMain
'Create writers and set formating for xml
Dim myScripter As Scripter
'Represents a writer to write information to a string
Dim myStringWriter As New System.IO.StringWriter()
Dim myStringWriterTrans As New System.IO.StringWriter()
'Represents a writer that provides a fast, non-cached, forward-only way of generating streams or files containing XML data that conforms to the W3C Extensible Markup Language (XML) 1.0 and the Namespaces in XML recommendations.
Dim myXmlTextWriter As System.Xml.XmlTextWriter
Dim myXmlTextWriterTrans As System.Xml.XmlTextWriter
Dim myXmlWriterSettings As XmlWriterSettings
Sub New()
myScripter = New Scripter
myXmlTextWriter = New System.Xml.XmlTextWriter(myStringWriter)
myXmlTextWriterTrans = New System.Xml.XmlTextWriter(myStringWriterTrans)
myXmlWriterSettings = New XmlWriterSettings
myXmlWriterSettings.OmitXmlDeclaration = True
myXmlWriterSettings.ConformanceLevel = ConformanceLevel.Auto
myXmlTextWriter.Formatting = Formatting.Indented
myXmlTextWriter.Indentation = 2
End Sub
Public Sub Main()
'Get Server name from DTS connection object and store in variable
Dim oDTSASConnection As ConnectionManager = Dts.Connections("SARP_Cubes")
Dim sASServer As String = CStr(oDTSASConnection.Properties("ServerName").GetValue(oDTSASConnection))
'MsgBox("Server " & sASServer & " has been connected")
Dim oASServer As New Microsoft.AnalysisServices.Server
'Connect to the requested server
oASServer.Connect(sASServer)
'Get Database name from DTS connection object and store in variable
Dim sASDBName As String = CStr(oDTSASConnection.Properties("InitialCatalog").GetValue(oDTSASConnection))
Dim oASDatabase As New Microsoft.AnalysisServices.Database
'MsgBox("InitialCatalog " & sASDBName & " has been found")
'Get database sASDBName and store in variable
oASDatabase = oASServer.Databases.GetByName(sASDBName)
'MsgBox("Database " & sASDBName & " has been connected")
'Get Cube
Dim CubName As String
If Dts.Variables.Contains("CubName") = True Then
CubName = CType(Dts.Variables("CubName").Value, String)
End If
Dim oASCube As New Microsoft.AnalysisServices.Cube
'MsgBox("Database " & sASDBName & " has been connected")
'Create a variable to store the create cube ASSL-script
'Dim sASSLCreateCub As String
'Store the create script in myXmlTextWriter
myScripter.ScriptCreate(New MajorObject() {oASDatabase}, myXmlTextWriter, False)
myXmlTextWriter.Flush()
'Create a string in order to manipulate the XML-string and append the Batch and process element
Dim sASSLString As String
sASSLString = "" & myStringWriter.ToString & "ProcessFull SARP_Cubes"
'Make a database conenction through connection manager
'Get Server name from DTS connection object and store in variable
Dim oDTSDBConnection As ConnectionManager = Dts.Connections("METADATA")
Dim sDBServer As String = CStr(oDTSDBConnection.Properties("ServerName").GetValue(oDTSDBConnection))
Dim sDBDatabase As String = CStr(oDTSDBConnection.Properties("InitialCatalog").GetValue(oDTSDBConnection))
Dim oBuilder As New SqlConnectionStringBuilder()
oBuilder.DataSource = sDBServer
oBuilder.InitialCatalog = sDBDatabase
oBuilder.ConnectTimeout = 1000
oBuilder.IntegratedSecurity = True
oBuilder.ApplicationName = "InsertASSLScript"
Dim oDBConnection As New SqlConnection(oBuilder.ConnectionString.ToString)
' Create Sql Command
Dim cmd As New SqlCommand("SSAS_TEST.InsertASSLScript", oDBConnection)
cmd.CommandTimeout = 60
cmd.Connection = oDBConnection
cmd.CommandType = CommandType.StoredProcedure
' Add parameters and their values
cmd.Parameters.Add(New SqlParameter("@COUNTRY_CODE", SqlDbType.VarChar, 255)).Value = "999"
cmd.Parameters.Add(New SqlParameter("@CUBE_XMLA", SqlDbType.VarChar)).Value = sASSLString
cmd.Parameters.Add(New SqlParameter("@DATABASE_ID", SqlDbType.VarChar, 255)).Value = sASDBName
cmd.Parameters.Add(New SqlParameter("@CUBE_ID", SqlDbType.VarChar, 255)).Value = "ALL CUBES"
' Open the connection
oDBConnection.Open()
' Execute the command
cmd.ExecuteNonQuery()
' Clean Up
myStringWriter.Close()
myStringWriterTrans.Close()
'myStringReader.Close()
'myXMLReader.Close()
myXmlTextWriter.Close()
'oDBConnection.Close()
oASServer.Disconnect()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
2. We manipulate the ASSL-script in order to create the cube that we want to have as local cube
3. We extract the final ASSL-script from the database through an "Execute SQL Task" with a XML Result Set. The SQL use in the task is:
Code Block
SELECT cast(CUBE_XMLA as varchar(max))
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')
I have also tried
Code Block
SELECT CUBE_XMLA
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')
and
Code Block
SELECT CUBE_XMLA
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500')
FOR XML AUTO
I always get the same error:
"Execute SQL Task: Executing the query "SELECT cast(CUBE_XMLA as varchar(max))
FROM SSAS_TEST.CUBE_XMLA
WHERE (COUNTRY_CODE = '500') " failed with the following error: "/ROOT/*[local-name()="Batch" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Create" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="ObjectDefinition" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Database" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Cubes" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Cube" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="MeasureGroups" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="MeasureGroup" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]/*[local-name()="Source" and namespace-uri()="http://schemas.microsoft.com/analysisservices/2003/engine"][1]
Type '{http://schemas.microsoft.com/analysisservices/2003/engine}MeasureGroupBinding' is not found in Schema."
Does anyone know how to handle this?
If we could use this XML variable, we will use another Script task to generate our Local Cube(s). The script task looks like this:
Code Block
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices.AdomdClient
Imports System.Data.SqlClient
Imports System.Xml
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'Declare variables
'Connection
Dim conn As AdomdConnection
'Command
Dim cmd As AdomdCommand
'Cellset
Dim cst As CellSet
Dim strFilename As String = "D:TestLocal.cub"
Dim strSource As String = Dts.Variables("ASSLCreateScript").Value.ToString()
'*-----------------------------------------------------------------------
'* Open connection.
'*-----------------------------------------------------------------------
Try
' Create a new AdomdConnection object, providing the connection
' string.
conn = New AdomdConnection("Data Source=" & strFilename)
' Open the connection.
conn.Open()
Catch ex As Exception
Throw New ApplicationException( _
"An error occurred while connecting.")
End Try
Try
'*-----------------------------------------------------------------------
'* Open cellset.
'*-----------------------------------------------------------------------
' Create a new AdomdCommand object, providing the ASSL query string.
cmd = New AdomdCommand(strSource, conn)
' Run the command and return a CellSet object.
cst = cmd.ExecuteCellSet()
'*-----------------------------------------------------------------------
'* Release resources.
'*-----------------------------------------------------------------------
conn.Close()
Catch ex As Exception
' Ignore or handle errors.
Finally
cst = Nothing
cmd = Nothing
conn = Nothing
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I hope that someone can help us. We browse the net without result,
Thanks in advance.
Hi,
I have a 'Execute SQL Task' in my 'control flow', my 'Execute SQL Task' will return a value which I am assigning to a variable. Based on the value of the variable, I need to control my other flows. If the variable's value is 1 then I should invoke a dataflow, else I should write a failure error message in event viewer. Please could someone provide some inputs on how this can be done.
'Execute SQL Task' ----->value 1 ------>data flow to be executed
'Execute SQL Task' ----->value !=1 ------> write some error message in the event viewer and no tasks should be executed after that.
Thanks
raj
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is:
select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double.
I have nothing configured in the "Parameter Mapping" tab.
I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Thanks for the help
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned). There is no problem to achieve that.
But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. "
I tried to change the type of variable around but with no luck.
Could anyone help with this issue? Thanks!
Hi friends,
I couldn't find links for this issue.
1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.
2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.
How can we achieve this?
Thanks
Subhash Subramanyam
Currently I am running a SSIS package scheduled daily at 7 A.M. It expects two feed files from two different folders. The first step in my package will rename the input files in those folders to names which the package can understand. I have created two variables in my package to read the files with those file names. I used these variables in connection managers.
If any of these folders doesn't have input files when the package runs, the package will fail.how to make the package the run successfully even there is no input feed?
How can a tell if a tak completed succesfully from a stored procedure?
I have a task which is executed from a stored procedure. The sp_runtask only returns whether the task started successfully. How can I tell if it completed successfully?
Thanks
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
Is it possible to retrieve a particular row from a result set? For eg if my query returns 5 rows and i want to just retrieve the 3rd row from the result....is it possible? If yes...can someone tell me the syntax for it....would appreciate the gr8 help...
Thanks,
SQL Novice
I am using SQL Server 2005.
I have a table with records like this:
Date EmpID ADCode ADAmount
----------------------------------------------------------------------
01-Jul-07 101 GPF 150
01-Jul-07 102 GPF.ADV 100
01-Jul-07 103 GPF 250
01-Jul-07 103 GPF.ADV 200
01-jul-07 104 GPF 300
I want to show results like this using a single SQL query:
Date EmpID GPF GPF.ADV
-----------------------------------------------------------
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 250 200
01-Jul-07 104 300 0
I tried:
select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord
It is showing like this:
Date EmpID GPF GPF.ADV
-----------------------------------------
01-Jul-07 101 0 0
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 0 0
01-Jul-07 103 250 0
01-Jul-07 103 0 200
It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
A.
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
Hi I'am practically new in C#, so I want to ask you guys some question.Let say I have this query:"Select EmployeeID, EmpName from PI_Employee"How can I retrieve the result from EmployeeID column and EmpName column and put it into collections of array, so I can call the array and use it again in another function. Can I possibly do that in C# ? if so, how ? please help me guys, I'm on the edge of nervous wreck in here so I could use a little help, any kinds of help. Thanks. Best Regards.
View 2 Replies View RelatedHow to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:
SNo SudentName Marks
001 Ashok kumar 67
002
Anderson
70
003 Alfred
60
004 Ameeruddin
65
005 Kalyan
Kumar.
69
Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.
How to write this query in SQL Server.
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this:
1. SELECT name FROM table1
2. SELECT name FROM table2
3. Join the two results together and order them alphabetically
4. Return the result set
I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
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
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 Related