ole db source editor "preview" throwing an error

i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?

thanks in advance.

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

|||

Brian Knight wrote:

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

thanks for the reply. i wonder if this is a bug or a "feature".|||

Not sure it's really either. It was the same way in DTS. Essentially the provider can't parse the literal query. Probably the ultimate answer is to perform the preview and parse like Reporting Services does where it pops open a box asking for the variable values you'd like to parse. As it stands, SSIS or DTS doesn't have enough info to perform a parse.

Brian

ole db source editor "preview" throwing an error

i have a data flow configured to use a ole db source. the ole db source uses an ole db connection manager pointing to the adventureworks db which suceeded when i tested its connection. the data access mode of the ole db source is "sql command". below is the sql command text:

SELECT SpecialOfferID, Description
DiscountPct, Type, Category, StartDate,
EndDate, MinQty, MaxQty, ModifiedDate
FROM Sales.SpecialOffer
WHERE ModifiedDate >= ? AND ModifiedDate < ?

this query uses 2 paramaters, each of which is mapped to a datetime variable which falls with the range of the ModifiedDate column as follows:

Parameter0: User::ExtractStartDate

Parameter1: User::ExtractStopDate

ExtractStartDate is set to 7/1/2001 and ExtractStopDate is set to 3/31/2004. however, i get the following error when i press the preview button in the ole db source editor: "there was an error displaying the preview. additional information: no value given for one or more required parameters (microsoft sql native client)".

as far as i can tell, i have the ole db source configured correctly. thus, i can't figure out why this error is being generated. has anyone else experienced this issue? if so, were you able to resolve it? is this a bug?

thanks in advance.

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

|||

Brian Knight wrote:

Hi Duane, once you use parameters in your query, the query will not be able to parse or preview. It's a pretty normal hassle you'll get used to :).

Brian

thanks for the reply. i wonder if this is a bug or a "feature".|||

Not sure it's really either. It was the same way in DTS. Essentially the provider can't parse the literal query. Probably the ultimate answer is to perform the preview and parse like Reporting Services does where it pops open a box asking for the variable values you'd like to parse. As it stands, SSIS or DTS doesn't have enough info to perform a parse.

Brian

OLE DB Command Error SSIS

Hi,

when we are using OLEDB Command we are getting the following error?

when we use sql server as the source its working fine .but its throwing error when we try to connect the orcale database .

Error at Data FLOW TASk[OLE DB Command[3863]]: An OLE DB error has occured :0x80040E51.

An OLE DB record is available. Source : "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51

Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".

ERrror at Data Flow Task [OLE DB Command [3863]]: Unable to retrieve destination column description from the Parameter of the SQL Command

Thanks & Regards

Jegan.T

This is an OLE DB Error. My first suggestion would be to try Oracle's own driver instead of the Microsoft one.

-Jamie

|||

Hi Jamie,

Do we need to install orcale driver separtely or its comes with SSIS ?

Thanks

Jegan.T

|||

Seperately. It comes with Oracle.

|||

Hi Jamie

Thanks for the suggestion . i'll give a try and get back to you..

Jegan.T