RecordSet into a DataFlow Task

In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.

What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.

What am I missing that would make this easy?

...cordell...

if you need to have control on every row of the result set you may be on the need of using a data flow task(source, transforms and destination components). Would you give an example of what you are trying to accomplish after getting the 2000 rows?

|||

In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.

|||

Cordell Swannack wrote:

In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.

What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.

What am I missing that would make this easy?

...cordell...

This shows you how to do it:

Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?

-Jamie

|||

Cordell Swannack wrote:

In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.

I would wager you can accomplish all of this with a combination of the conditional split, multicast and/or derived column components.

Even if you can't, you would be better off employing a script component to parse through the data - there is still no need to put it into a variable first.

-Jamie

|||

Jamie Thomson wrote:

Cordell Swannack wrote:

In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.

What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.

What am I missing that would make this easy?

...cordell...

This shows you how to do it:

Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)

I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?

-Jamie

I was going to suggest something similar. In general you would use data flows to perform row by row operations; like transformations, splits, sorts, etc. However the control flow may be better place to row set based operations; like an update, select into, etc.

In you particular case, use a dataflow task and then drop a OLE DB source component, any required transform and the detination component.

|||

Helpful suggestions....

While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)

However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.

But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.

...cordell...

|||

Cordell Swannack wrote:

Helpful suggestions....

While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)

However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.

But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.

...cordell...

You're absolutely right. This IS a problem with sprocs. And I agree that this sounds like a good justification for using the Execute SQL Task route. However, I suggest another workaround here:

Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)

that you may wish to employ that will enable you to use the OLE DB Source component.

-Jamie

|||

I found your blog entry on the subject just as I was receiving your email.

The reason why a UDF doesn't work for my solution is the amount of transformation processing that must be performed upon the data. Basically I have 10,000's of data records that are processed and then randomized into a laboratory trial groups which are then distributed out to various computer systems for analysis.

I ended up changing around the stored procedure to create a table in the database...and then once the SSIS work has completed...I drop the table. A less than perfect solution...but it gets the job done and it is time to move on.

Thank you Jamie for your time to answer my questions and your patience.

...cordell...

Recordset does not open with Table Variables

Hello!

I have made an stored procedure that receives 2 parameters and returns a resultset. The resultset is populated from a select made from a table variable declared on that procedure:

select * from @.MyTable

Now, the stored procedure works as expected when invoked on the Query Analizer; but when using a Visual Basic application that uses ADO 2.7, the Recordset object does not open.

What is wrong?

Thanks a lot in advance.Any error?|||Any error?|||Thanks for your reply.

The recordset object does not open; however, a runtime error does not occur.

The visual basic code is very straightforward and has been used with other kind of stored procedures:

Public Sub LoadData()
on error goto E:
Dim objConnection As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRecordset As ADODB.Recordset
Set objConnection = New ADODB.Connection
objConnection.CursorLocation = adUseClient
objConnection.ConnectionString = m_strConnectionString
objConnection.Open
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "myStoredProcedure"
objCommand.Parameters("@.myParameter1").Value = m_varValue1
objCommand.Parameters("@.myParameter2").Value = m_varValue2

Set objRecordset = objCommand.Execute
If objCommand.Parameters(0).Value = 0 Then
do while not objRecordset.EOF
debug.print objRecordset!myField1
objRecordset.MoveNext
loop
End If
Exit sub
E:
MsgBox Err.Description

End Sub

I do not understand why the recordset is not opened. The only difference with other kind of stored procedures that I have used is that the SELECT statement is made from a Table variable:

select * from @.MyTable

The Query Analyzer returns values.

What could be wrong?|||When using Table variable or Temp tables, it is necesary to write "SET NOCOUNT ON" on the top of the stored procedure.

I read that in "PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB", a Microsoft Knowledge Base Article (235340).

http://support.microsoft.com/support/kb/articles/Q235/3/40.ASP

:-D

Recordset Counts

I am trying to count the number of records in a recordset I have created. It always returns a value of -1 nomatter how many records are actually there. I have other operations working fine using similar code, but this one will not work correctly. Can anyone help. Here is the code.

Set ashcontentcountcmd = New ADODB.Command
ashcontentcountcmd.ActiveConnection = ashconn
Set ashcontentcountrs = New ADODB.Recordset
ashcontentcountrs.ActiveConnection = ashconn

ashsource = lstash.List(lstash.ListIndex)

ashcontentcount = "select ID from Flyash where Source ='" + ashsource + "'"

ashcontentcountcmd.CommandText = ashcontentcount ashcontentcountrs.Open ashcontentcountcmd

Debug.Print ashcontentcountrs.RecordCount

Thank you in advance for any help.

MichaelYou need to make it a client-side cursor to return the recordcount or your cursor is the wrong type.|||I am new to this stuff. I don't know what you mean by a client-side cursor. This is an Access database I am connecting to.

RecordCount -1

Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
http://www.aspfaq.com/2193
http://www.aspfaq.com/
(Reverse address to reply.)
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
|||Thanks you. I am still not clear how to change the code in order to fix it?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
> http://www.aspfaq.com/2193
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
> news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
> Hello,
> I am using the function below to get some values form database.
> However for RecordCount it always returns -1.
> Any clues hot to fix it?
> Thx
> YuriW
> Function RunSQL (sConnection_String, sSQL_QUERY)
> Dim db_object
> Dim sSQL_string
> 'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
> sSQL_string = sSQL_QUERY
> ' Create the conection object.
> Set db_object = CreateObject("ADODB.Connection")
> ' or use this method if a DSN entry was created.
> db_object.Open(sConnection_String)
> ' Get the recordset returned from a select query.
> Set recordset = db_object.Execute(sSQL_string)
> ' Display the results of the query.
> sOut = recordset.RecordCount
> RunSQL = sOut
> ' Close the database connection.
> db_object.Close
> Set db_object = Nothing
> End Function
>
|||Maybe you need to look at the article again?
http://www.aspfaq.com/
(Reverse address to reply.)
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
news:dnoxc.68491$iR3.59055@.newssvr29.news.prodigy. com...
> Thanks you. I am still not clear how to change the code in order to fix
it?
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
>
|||Recordcount is not supported with the default forward-only cursor. You can try to set the type of cursor before you excute your SQL code.
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
|||Got it, thx!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
> http://www.aspfaq.com/2193
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
> news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
> Hello,
> I am using the function below to get some values form database.
> However for RecordCount it always returns -1.
> Any clues hot to fix it?
> Thx
> YuriW
> Function RunSQL (sConnection_String, sSQL_QUERY)
> Dim db_object
> Dim sSQL_string
> 'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
> sSQL_string = sSQL_QUERY
> ' Create the conection object.
> Set db_object = CreateObject("ADODB.Connection")
> ' or use this method if a DSN entry was created.
> db_object.Open(sConnection_String)
> ' Get the recordset returned from a select query.
> Set recordset = db_object.Execute(sSQL_string)
> ' Display the results of the query.
> sOut = recordset.RecordCount
> RunSQL = sOut
> ' Close the database connection.
> db_object.Close
> Set db_object = Nothing
> End Function
>
|||In "Display the results of the query" you should iterate through recordset
with MoveFirst/MoveNext. Add counter into this cycle
dim counter
counter = 0
rs.MoveFirst
while not rs.EOF
... display record
counter = counter + 1
rs.MoveNext
wend
'here counter contains number of records
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> ??/?? ?
?? ??: news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function