question

RyanRigby-9160 avatar image
0 Votes"
RyanRigby-9160 asked Monalv-msft edited

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

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Monalv-msft Thank you for taking the time to reply. I am already doing exactly what you explained to do in your answer. In my original post I was trying to explain that I define the query in the MainQuery variable, and then use the variable in the OLE DB Source using the 'use command from variable option', and I can't figure why I am getting the error I displayed. One of my troubleshooting steps was to print the MainQuery variable to a file. When I copy the query from the file and paste it in SSMS, the query executes properly, so I am confident that I don't have a misformed query in the variable.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @RyanRigby-9160 ,

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.




oledbsource.png (30.6 KiB)
variables.png (12.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RyanRigby-9160

Could you please share the example data of the variables?

May I know if you can preview the data in the OLEDB source?

Best regards,

Mona

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.