Showing posts with label oledb. Show all posts
Showing posts with label oledb. Show all posts

Wednesday, March 21, 2012

Problems with Parameterized insert SQL with OLEDB Destination

Hello,

I've searched around and can't find any references to the problem I'm having. I'd appreciate any ideas or input.

I'm trying to use the OLEDB Destination for an insert at the end of a long data flow. I need to parameterize the input, and for some of the columns I need to use literal values instead of parameters. It seems like this should be the most common thing in the world, but I'm at a loss to get it to work.

I type in the SQL statement just like I would with an OLEDB Command transformation, with the ? character for the appropriate columns in the VALUES clause. However, when I try to use Parse Query I get this error:

"Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command."

OK, so I start searching around for ways to set the parameter information. Nada. On the Mappings tab the parameter list is empty. I check MSDN and it says this:

"If you have entered a parameterized query by using ? as a parameter placeholder in the query text, use the Set Query Parameters dialog box to map query input parameters to package variables."

Set Query Parameters dialog box? I don't see this anywhere. What am I missing?

The options with the SQL Server Destination seem even more limited, as I don't see any way to use a SQL statement or stored procedure.

For the moment I'm going to stub this off with an OLEDB Command transformation with a downstream Trash desintation, but hopefully that's only going to be temporary.

Thanks,
Dan

Update: I tried using a stored procedure instead of a parameterized SQL statement, and this does not work either--though with different error info:

"Invalid parameter number"

If anyone has any idea what's going on here, I could really use the help. Is there a known bug with the OLE-DB Destination not being to handle parameterized SQL statements or stored procedure calls? Does anyone know about this mysterious "Set Query Parameters" dialog box?

Thanks,
Dan

|||You have got the wrong end of the stick with the OLE-DB destination. The SQL Command option allows you to target "results of an SQL statement", that is not the same as a parameterised command. Result set means a SELECT. To target a comand such as UPDATE or DELETE you need to use the OLE-DB Command as you did previously. The Command Tx does not require any subsequent transformations, so no need for the Trash destination.|||Thank you for your post, Darren. I appreciate your taking the time.

Knowing that OLE-DB Command transform does not require a downstream destination helps me a lot. Somewhere I had picked up the idea that all paths through the data flow needed to terminate in a destination.

That said, I still say something is very strange in all this. Here is a description of the OLE-DB Destination from MSDN/BOL:

"The OLE DB destination loads data into a variety of OLE DB-compliant databases using a database table or view or an SQL command."

Sounds about right to me. So I'm at a loss to resolve that idea with the statement in the same article, to which you referred, that the SQL option is for "The results of an SQL statement." Huh? How does calling a SELECT statement coincide with the idea of "loading date into a variety of OLE-DB compliant databases?"

This gets even more confusing: If you click through to the "OLE DB Destination Editor (Connection Manager Page)" article from the main article, you can find this explanation that the SQL command is to "Load data into the OLE DB destination by using a SQL query."

Then another contradiction comes in: the Build Query button in the OLE-DB Destination editor defaults to building a SELECT statement.

My point in all this is not to prove you wrong or anything, Darren, but rather to submit that I think we have a combination design and documentation bug here: somewhere along the line the SQL capabilities of the OLE-DB Destination got confused with the data-selecting functionality of the OLE-DB Source. Or am I missing something?

Thanks again,
Dan

|||

I certainly think the docs are not very clear. I think I know what it does, and what the capabilities of the SQL option is, but may be wrong. I have a habit of not reading Books Online when I (think I) know what it does already. I couldn't find anything particuarly strong in support of my description other than that snippet. Hopefully sombody else will give their opinion on this, but either way you should submit some documentation feedback using the link on the page.

If you are right in your expection of how it works, then feedback that the documention is not detailed enough to allow you to get it working properly. If I am right then feedback that the docs gave you the wrong impression. All these references to the docs mean Doug will be along in a minute to clarify for us! Sure he will still appreciate the feedback being logged.

|||Well, I started to post the feedback to the MSDN page, but when I tried to hit the Enter key to make a line break in my message it submitted it prematurely, and after that the feedback form was gone. Oh, well. Hopefully someone reading this thread will be able to use this information.

That said, setting aside the documentation issues, what do you think of this concept of a SQL SELECT statement with a Destination? Assuming this is not a bug, what would be the purpose for this functionality?

My theory is that this is something akin to a copy-and-paste bug, in which a feature from the OLE-DB Source was reused in the Destination. But I'm ready and willing to be wrong.

Dan

|||

I always do my feedback from within my local copy of Books Online. After the rating option this just opens an email, much easier.

You can insert data into a table. You can insert data into a view. A view is just a SELECT statement, so my take is the OLE-DB provider adds some additional functionality that pseudo materializes the SELECT. After all it just needs the meta-data and it can get this from a select as well as a table. The data is then processed into the base table. There is probably more information in the depths of the OLE-DB specifications, but I have always found life is too short for those. Why not just try it and run a profiler trace at the same time. Compare the underlying statements issued by the different methods will give you a clearer understanding of the differences, as well as the similarities.

Problems with Oracle character setting

Hi there,

I am using an ADO Oracle Connector to store Oracle data to an SQL Server.
When I map the input data to the OLEDB destination I get the Error msg that
unicode (DT_WSTR) cannot be mapped to 1292 character code (DT_STR).

To solve this I put a Data Conversion Transformation between Source and Destination.
That would mean a lot of work if I couldn't map the data directly from Source to Destination
for all the tables of my project.

I checked the NLS-Settings from the Oracle db:
select * from sys.v_$nls_parameters
NLS_CHARACTERSET -> WE8ISO8859P1

It's really a mystery to me why the DataReader converts Latin-1 to unicode which has to be
converted to Latin-1 again.
Could someone please help me out?

Fridtjof
The problem here is with managed code. The CLR doesn't have a ANSI type string so all strings are converted to Unicode. So in actuality, it is not the datareader that does the conversion but Oracle's ADO.NET connector. Obviously, knowing this doesn't really help you out all that much though. Is there any way you can make the SQL Server table be unicode instead of ANSI. If not then data conversion is the only solution other than to get an OLEDB driver for Oracle, since OLEDB does support ANSI directly.

HTH,
Matt|||Matt,

you're right. I've tried to download Oracle data via OLEDB which nags that it cannot read Oracle's character setting and assumes it to be 1292 (or was it 1252?). But it downloads the data correctly.

Unfortunately I cannot set the password property in a Package configuration. See this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161564&SiteID=1

This way I'm not getting much further. One have to ponder between the different restrictions :(
Thanks anyway
Fridtjof

Monday, February 20, 2012

Problems when retrieving cubes schema using OleDb

Hello,

I'm using Analysis Service 9.0 OleDb provider to retrieve a schema from the data source as follows:

Code Snippet

olapConnection.GetOleDbSchemaTable(OLAPSchemaGuid.Cubes, new object[] { catalogName });

The problem is that I cannot get a cube list from some catalogs. For example, I can list cubes from the "OLAPTestCube" catalog, but I cannot do this from the "Adventure Works DW". It seems to me the problem appeared after upgrading SQL Server to SP2. What may cause this behavior?

Thank you,

I've got it. The solution was to call the OleDbConnection.ChangeDatabase method. It seems to me that GetOleDbSchema method works in scope of the current catalog.