Is there anyone who was able to successfully retrieve a full result set in Execute SQL Task?

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

Yes, I do it all of the time. What error messages are you receiving?

You need to populate a variable of "Object" type for starters. That is a common "gotcha."|||http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html|||

Oops, sorry. Wasn't able to mention it that I'm doing it programmatically. There is a result set property in the General tab and result Name and Variable Name in the Result Set tab in the Execute SQL Task editor. How will I be able to set this in the code?

Thanks.

|||

I haven't done this with a result set, but I have extracted results from calling an sp. The main difference is the result set type you define for the SQLTask.

This should have the basics of what you need -- Create a variable to hold the result, add the SQL Task and bind the result set to the variable.

The colon is really in the code, [removethis] is not.

PrivateConst SetupStepKey AsString = "SetupStepKey"

Package.Variables.Add(SetupStepKey, False, User, 0)

'Add SQL Task to DataFlow sequence container - Get Package Execution Details

exe = sequenceContainer.Executables.Add("STOCK[removethis]:[removethis]SQLTask")

Dim thGetDetails As TaskHost = CType(exe, TaskHost)

thGetDetails.Name = "GetExecutionDetails"

thGetDetails.Description = "Get StepKey, Extract, Insert"

thGetDetails.Properties("SqlStatementSourceType").SetValue(thGetDetails, SqlStatementSourceType.DirectInput)

thGetDetails.Properties("SqlStatementSource").SetValue(thGetDetails, JobDetailsSQL)

thGetDetails.Properties("ResultSetType").SetValue(thGetDetails, ResultSetType.ResultSetType_SingleRow)

thGetDetails.Properties("Connection").SetValue(thGetDetails, SQLDestination)

'Parameter Binding

Dim getDetailsTask As ExecuteSQLTask = TryCast(thGetDetails.InnerObject, ExecuteSQLTask)

'Result Set Bindings

getDetailsTask = TryCast(thGetDetails.InnerObject, ExecuteSQLTask)

resultSet = getDetailsTask.ResultSetBindings.Add

resultSet.DtsVariableName = UserScope & SetupStepKey '"User:[removethis]:[removethis]SetupStepKey"

resultSet.ResultName = "SetupStepKey"'This is the column name that is returned

|||Thanks Verbal!=)