Unanswered: OPENROESET/sp_addlinkedserver with DTSPackageDSO
I did create a simple package, named 'Pkg2' using SQLServer connection and Execute SQL Task, which has a simple query "Select * from XXX'. This has one Global variable id, mapped to key of XXX. When I run this package using DTSRun and Enterprise manager, it works.
When I try to run it using external sources like OPENROWSET and sp_addlinkedserver, it is returning following error:
"Server: Msg 7350, Level 16, State 2, Line 1
Could not get the column information from the OLE DB provider 'DTSPackageDSO'.
[OLE/DB provider returned message: The named Step could not be found or could not supply metadata. Ensure the Step and all referenced objects exist.]"
This is my command, Select id from OPENROWSET('DTSPackageDSO', '/SSAUNIX2-NT50 /Usa /NPkg2', 'Select * from Pkg2.DTSStep_DTSExecuteSQLTask_1')
or sp_addlinkedserver MyDTSPackage, 'PackageName', 'DTSPackageDSO', '/FC:\cvs_saunix\Pkg21.dts' and 'select * from MyDTSPackage...DTSStep_DTSExecuteSQLTask_1'
sp_addlinkedserver returns, 1 record added message twice instead server added.
Originally posted by Satya
Edit the package and set transformation to be a DSO provider under workflow options.
This is what I did: Right click on the task, select the workflow->workflow properties, then option tab and in execution, check DSO Service Provider. Result:
"Could not get the column information from the OLE DB provider 'DTSPackageDSO'.
[OLE/DB provider returned message: No Step name was specified and the provider was unable to determine a single Step returning a result set.]"
Is there any problem with the way, I am creating the package? Can you provide me one working example?