Apr
7
2009

Setting the DataType of IDTSParameterBinding objects (Execute SQL Task)

If you're writing Integration Services code that uses the Execute SQL task, you also have to create parameters programmatically. The ParameterBindings property of the ExecuteSQLTask returns an IDTSParameterBindings collection of IDTSParameterBinding objects, each of which has a DataType property. But the DataType is just an integer. Where do the values come from? Why doesn't it just get its values from an enumeration that's clearly documented?

Well, the values for the DataType of an IDTSParameterBinding depend on the type of connection manager used in the Execute SQL task, as follows:

  • For an ADO.NET or SQLMOBILE connection, the data types are from the System.Data.DbType enumeration.
  • For an OLE DB or EXCEL connection, the data types are OLE DB data types. Also consult the oledb.h header file in the Windows SDK.
  • For an ADO connection, the data types are from the ADODB.DataTypeEnum enumeration.
  • For an ODBC connection, the data types are ODBC data types. Also consult the odbcss.h header file in the Windows SDK.

I've provided links above to the API reference pages in the MSDN Library. Unfortunately, the reference topics for enumerations don't include the integer values that you need.* At least you'll know which assembly and which type to open in Object Browser to find their numeric values.

*<rant>This has always infuriated me about the API documentation, because you need those integer values more often than you think. And it would be effortless for the reflection process that's already being run to crank them out automatically!.</rant>


Please comment on my blog!

You don't have to sign up or sign in - anonymous comments are enabled. I "moderate" the comments to delete spam before you see it, but otherwise, I publish your comments immediately. You can also email me at dougbert@dougbert.com. Thank you!

-Doug

Comments (3) -

Duane Douglas

This is a very useful blog post.  I also find it frustrating to work with sparse documentation...especially when I'm on deadline. Doh!


harika

Hi doug,


could you please say me how to  return result set form sql task  ,i am unable to get it.


this is my code.


Dim taskSQL As TaskHost = myPackage.Executables.Add("STOCK:SQLTask")


       Dim strsql As String


       strsql = "SELECT  ADDBY, ADDDTTM, ADDR, BGTNO, BNFTRATE, CITY, COMMENTS, CREWLDR, DATAGRP, DATAGRPFLG, DAYPHN, DEPT, EMAIL, EMERGINFO, "


       strsql = strsql & " EMERGNAME1, EMERGNAME2, EMERGNAME3, EMERGPHN1, EMERGPHN2, EMERGPHN3, EMERGREL1, EMERGREL2, EMERGREL3, EMPFIRST,"


       strsql = strsql & " EMPID, EMPLAST, EMPMI, EVEPHN, EXPDATE, FAX, HIREDATE, INSPFLAG, MOBILE, MODBY, MODDTTM, PGR, PGRPIN, RATE, SECT, SSN, STATE, "


       strsql = strsql & "SUPR, SUPRFLAG, SYSFLAG, ZIP, cntctkey, rownum"


       strsql = strsql & "  FROM IMSV7.EMPLOYEE"


       strsql = strsql & "   WHERE     (REPLACE(EMPID, ' ', '') <> '')"


       taskSQL.Properties("Name").SetValue(taskSQL, "Select Employee")


       Dim ObjEmployeeInfo As Variable = myPackage.Variables.Add("ObjEmployeeInfo", False, "User", Nothing)


       ObjEmployeeInfo.Value = New Object


       ObjEmployeeInfo.EvaluateAsExpression = False


       ObjEmployeeInfo.Expression = String.Empty


       taskSQL.Properties("BypassPrepare").SetValue(taskSQL, False)


       taskSQL.Properties("Connection").SetValue(taskSQL, "MyOLEDBConnectionSrc")


       taskSQL.Properties("SqlStatementSource").SetValue(taskSQL, strsql)


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


       taskSQL.Properties("ResultSetType").SetValue(taskSQL, ResultSetType.ResultSetType_Rowset)


       ''taskSQL.Properties("ResultSetBindings").SetValue(ObjEmployeeInfo, taskSQL)


       taskSQL.ExecValueVariable = ObjEmployeeInfo


please correct me ...


dougbert

harika,


I regret that I don't know the answer offhand, and a single blogger is not the best source for technical support. I encourage you to post your question in the SSIS forum on MSDN at social.msdn.microsoft.com/.../">social.msdn.microsoft.com/.../threads, where you'll reach a huge audience of SSIS experts including the product team at Microsoft. Thank you,


-Doug


Pingbacks and trackbacks (1)+