Using a Variable in SSIS(BIML SCript) - Error - "Command text was not set for the command object.

Jan 8, 2015 at 3:19 AM
Hi All,

I am using a OLE DB Source in my dataflow component and want to select SQL Query from the master table I have created variables v___Archivequery__ String packageLevel (to store the query).

__<Variable Name="V_Archivequery" DataType="String">
  SELECT a.*, b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum
  FROM dbo.ImportBBxFbcci a LEFT OUTER JOIN Archive.dbo.ArchiveBBxFbcci b
  ON (SUBSTRING(a.Col001,1,4) + SUBSTRING(a.Col002,1,10)) = b.BBxKey
  Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL)
 </Variable>__
I am assigning this query to the v_Archivequery variable, "SELECT a.*, b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum
FROM dbo.ImportBBxFbcci a LEFT OUTER JOIN Archive.dbo.ArchiveBBxFbcci b
ON (SUBSTRING(a.Col001,1,4) + SUBSTRING(a.Col002,1,10)) = b.BBxKey
Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL)
"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, v_Archivequery .

But when I am generating the package and when running I am getting bewlo errror

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

Please let me know where am going wrong?

Thanks in advance.
Coordinator
Mar 17, 2015 at 6:42 PM
Can you retest in the new BIDS Helper 1.7.0 and let us know if this is still a problem?