SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 error when transfer variable string to OLE DB Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

boathead
Starting Member

USA
5 Posts

Posted - 06/09/2013 :  18:48:43  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/10/2013 :  08:06:10  Show Profile  Reply with Quote
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
Go to Top of Page

boathead
Starting Member

USA
5 Posts

Posted - 06/10/2013 :  12:22:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/10/2013 :  12:54:14  Show Profile  Reply with Quote
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
Go to Top of Page

boathead
Starting Member

USA
5 Posts

Posted - 06/11/2013 :  15:47:14  Show Profile  Reply with Quote
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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/12/2013 :  00:53:31  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000