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.