Problem With Ole DB Source

Question

Problem With Ole DB Source

Erik Harrelson

Old Hand

Points: 397
More actions
June 30, 2009 at 8:45 am

#132356

Hello everyone,
I am trying to create an OLE DB Source which will get its SQL from a variable I have created. I created a script task in control flow with the following code:
Dts.Variables("User::SqlCmd_Variable1").Value =
"SELECT *
FROM(schema.table)
WHERE field1 IN ('a1', 'b1', 'c1', 'd1')
AND field2 BETWEEN '" & Dts.Variables("User::ProcessDateBegin").Value.ToString() & "'
AND '" & Dts.Variables("User::ProcessDateEnd").Value.ToString() & "'"
The SQL is intended to check for field1 to have any of those values, and for field2 (a datetime field) to be between ProcessDateBegin and ProcessDateEnd variables which I assigned values in an earlier Execute SQL task.
I then went into my dataflow and put data access mode as SQL command from variable, and assigned SqlCmd_Variable1 to the variable name. When I try to do anything after this I receive the following error message:
TITLE: Microsoft Visual Studio
------------------------------
Error at TASKNAME Data Flow Task [taskname Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
I have searched far and wide, but cannot find a good solution to this problem, any help would be greatly appreciated! 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 1

At design time when you set the OLE DB Command to use a variable, you need to have a valid statement in the variable so that the validation succeeds.

If you don't have this set already, try setting the variable value to "SELECT * FROM(schema.table) WHERE field1 IN ('a1', 'b1', 'c1', 'd1',)". This of course will change when the script task runs.

Incidentally, you don't have to use the script task to set the variable dynamically. You can set the variable using an expression which can make it easier to debug.

HTH

Kindest Regards,

Frank Bazan

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

You can also set the package or that item to DelayValidation = "True", what it means is that the variable doesn't need to have a valid command until the moment before the item is instantiated for use. When that is set to False then the component(s) is/are validated on package load, which seems to be a problem. You will find that property in the properties page under Execution. You WILL have to have a valid command in the variable during development though or it will be difficult to work with.

CEWII

Erik Harrelson Old Hand Points: 397 More actions · Answer 3

Hi Frank and Elliott, thank you for your responses.

Using delay validation unfortunately left me with the same error when I tried on the task or the entire package. I tried inserting my SQL as the value in my variable and got the following:

Error at Taskname Data Flow Task [Schema_Task Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E14 Description: "Token FULL LEFT CROSS INNER RIGHT EXCEPTION was not valid. Valid tokens: . SQLSTATE: 42601, SQLCODE: -104".

I am currently testing with giving the variable a value within an execute SQL statement using declare & set.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

This now looks to be an error in your DB2 SQL syntax.

Perhaps what you could do, is input the SQL command directly into the OLE DB Source, then when you have the command right you will have something to aim for when you build the command for the variable.

I'm not an expert with DB2, but I see in one of your IN statements you have a loose comma at the end which wouldn't be a valid statement with SQL server.

Cheers

Kindest Regards,

Frank Bazan

Erik Harrelson Old Hand Points: 397 More actions · Answer 5

I believe that Frank is right, although the syntax I posted with the extra comma (fixed now) was a mistake in my formatting to post here rather than within my actual SQL in the package. In any case, thanks for the help, I believe the issue here is with DB2 syntax rather than anything else and is something I will look into myself.