Error occurs when using SQL Command From Variable in SSIS OLE Db Source

Ryan Rigby 1 Reputation point
2021-02-26T23:17:37.93+00:00

I am using a SQL SERVER 2008 database and creating an SSIS package in VS 2017. Here is pseudo-code of what I am doing:

Execute SQL Task - Get list of dates
Foreach Loop Container - For each date
   Expression Task - create query in a variable
   Script Task - print query (not results of query) to file for debugging purposes
   Data Flow
      OLE DB Source - run query using SQL Command From File option
      Continue processing data

When I run the SSIS package, everything runs correctly until the Data Flow is reached. At that point I get the following error in the OLE DB Source:

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

Error at Data Flow Task [SSIS.Pipeline]: "Fetch Data" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

I have ensured that the query is formed correctly by printing the text of the query to file, then copying the text from the file into SSMS and executing it. I am really confused why I am getting this 'Command text was not set' error. Below is the expression that is used to define the query in the expression task (other than sanitizing the names of tables and columns, this is the exact expression used).

@[User::MainQuery] = 
"SELECT
    FRM.COL_01,
    P.COL_02, 
    FRM.COL_03 AS COL_03_FLAG,
    FRM.COL_04,
    CONVERT(varchar(8),tTO.COL_05,112) AS COL_05_WHEN,
    CAST(FRM.COL_06 AS VARCHAR(20)) AS COL_06_TYPE
FROM tblFRM FRM
FULL OUTER JOIN tblP P
    ON FRM.PK_COL = P.PK_COL
FULL OUTER JOIN tableTO tTO
    ON FRM.PK_COL = tTO.PK_COL
WHERE  FRM.TRANS_DATE = CAST('" + (DT_WSTR,100)@[User::DateRangeIterVar] + "' AS datetime)
     AND FRM.CLIENT_ID IN  (" + @[User::ClientIDList]  + ")
    AND (FRM.TECH_VARIABLE_CHG  = 1 OR FRM.PROF_VARIABLE_CHG  = 1)"

Can anyone see what may be causing this error? I am baffled.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,891 Reputation points
    2021-03-01T03:13:42.93+00:00

    Hi @Ryan Rigby ,

    Please put sql query in variable.

    Then set the data access mode as SQL command from variable in OLEDB Source.

    72710-variables.png
    72767-expressionbuilder.png
    72775-oledbsource.png

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Jeffrey Williams 1,886 Reputation points
    2021-03-07T17:41:21.98+00:00

    Based on the query - there is no reason to generate a query as a variable. It would be much easier to use the OLEDB Source with parameters - and map your date and client ID variables to parameters in the query.

    0 comments No comments