error when transfer variable string to OLE DB Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

error when transfer variable string to OLE DB Data

Author

Topic

boathead
Starting Member

5 Posts

Posted - 2013-06-09 : 18:48:43

I declared a string variable in SSDT, and assign a string to it as SqlString='select * from CustomizedFunction(Argument1,Argument2,Argument3)' SqlString is good since I've print it out in SSMS.

However, when SqlString is accessed in a OLEDB Data Source of a Data Flow. Error occurs
Hresult:0xC0202009
ERROR at Data Flow Task-LoadData[OLEDB Source[1]]:SSIS Error Code DTS_E_OLEEBERROR. An OLEDB error has occured. Error Code:0x80040E0C.
An OLEDB record is available. Source:"Microsoft SQL Server Native Client11.0" HResult:0x80040E0C Description:"Command text was not set for the command object."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 08:06:10

i think you've not set commandtype as text as error points. change the type in task properties

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

boathead
Starting Member

5 Posts

Posted - 2013-06-10 : 12:22:56

Thanks,visakh16. Could you tell me how to change the type? I looked into tasks, Sql Execute Task and Data source task. But I didn't find any option about the type. T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 12:54:14

its called "data source mode" in data source task and "SQLSourceType" in execute sql task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

boathead
Starting Member

5 Posts

Posted - 2013-06-11 : 15:47:14

I am using OLE DB Source. I don't find the option in Data source property. DirectInput is chosen for SqlStatmentSourceType in the execute SQL Task. Then I set a sql statement in SQLStatement in order to return a resultset, such as select ... as resultset. The resultset is bind to a variable A in Result Set tab so that the data source could use A in Data Access mode and variable name. But always fail.

I am still wondering this way even though another way can achieve my task

quote:
Originally posted by visakh16

its called "data source mode" in data source task and "SQLSourceType" in execute sql task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 00:53:31

DirectInput is chosen for SqlStatmentSourceType in the execute SQL

you need to set it as variable

I am using OLE DB Source. I don't find the option in Data source property

you've data source mode as a property. it will have values table name,sql command, tablename from variable, sql command from variable. you should be choosing sql command from variable and map correct variable in the next dropdown

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources