populate a rowset variable

Question

Post reply

populate a rowset variable

musalappa@gmail.com

Ten Centuries

Points: 1228
More actions
August 17, 2007 at 12:59 pm

#69676

Hi,
I am trying to populate table names matching a pattern into a rowset global variable using query in "exec sql task" . this package has only a sql connection and exec sql task. When I save this package, I get a type mismatch error. Does anybody have an idea as to what I should do to avoid this error.
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Stuart Taylor-461421 Valued Member Points: 63 More actions · Answer 1

I just had this problem last week and found this answer from MSDN:

Now my problem is how to export a rowset-global variable into an Excel Spreadsheet...

Anyway, here's the Link: http://msdn2.microsoft.com/en-us/library/aa216450(sql.80).aspx

And here's the Article:

How to save an entire rowset into a global variable (Enterprise Manager)

To save an entire result set of a SELECT statement into a global variable using the Execute SQL task

From the Connection toolbar, drag a Microsoft® OLE DB Provider for SQL Server connection to the design sheet.
In the Database list, click pubs.
From the Task toolbar, drag an Execute SQL task to the Data Transformation Services (DTS) design sheet.
In the Execute SQL Properties dialog box, in the Existing connection list, click the pubs connection just created.
In the SQL statement box, type the following:
```
SELECT *FROM titleauthor
```
Click Parameters, and then click Create Global Variables.
In the Name list, type Authors, and then in the Type list, click <other>. Leave the Value box empty.
Click the Output Parameters tab, click Rowset, and then in the Output Parameter Type list, select the Authors global variable.
This procedure assigns all records returned from the SELECT statement to be stored in the Authors global variable.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 2

Stuart,

The following script opens a GV rowset and builds a comma-separated string for each row. You could incorporate the FileSystemObject in to the record loop and write each line to a CSV file, will that do?

Set oRS = DTSGlobalVariables("myGVrowset").Value

oRS.MoveFirst

For j =1 to oRs.RecordCount

For i = 0 to oRS.Fields.Count -1

If oRS.Fields(6).Value = "" Then Exit For

fieldList = fieldList & oRS.Fields(i).Value & ", "

If i = oRS.Fields.Count -1 Then fieldList = fieldList & vbcrlf

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

james.ellingworth Valued Member Points: 62 More actions · Answer 3

Hi Adrian,

Just a query regarding your above script. Are you aware as to any memory restrictions for Rowset global variables?

I am working on a package which uses 6 GV's that receive output in the form of Rowsets and I am not sure, but I think it is falling over when memory becomes full. I have been testing with smaller rowsets (using TOP 10, 100, 500) and that works fine, but trying TOP 1000 it seems to fall over.

Cheers,

James