locked
cant develop ado.net source inside DF, I think cuz odbc connection cant be used to retrieve metadata RRS feed

  • Question

  • Hi.  I'm looking all over the web to figure this out.  Most complaints seem to be at run time, I cant even get the pkg developed.

    For STD 2008, I followed Todd's instructions at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/71c1d14d-c0d3-4469-96bf-3f7937565384 for using .NET Providers\ODBC...  to "plumb" an odbc connection to ssis for use by an ado.net data source component in a DF.

    When I go to the ado.net source's editor, it cant load the tables list, I think because it cant connect.  Even though its associated connection manager could.  Below are some facts that may or may not be relevant...

    -SSIS etc did build the connection string after I chose "Use Connection String" and hit "Build...'....but what it appends to that (or subtracts from) string (if anything) on an actual open is something I'd love to see after what I've gone thru.

    -When I hit ok and eventually try to get the ado.net source editor to pick a table from a list, it sits for many minutes on end before
    coming back with an error "Could not retrieve the table information for the connection manager...Object reference not set to...".

    -When I get out and then open the connnection manager again, I see "Use User or System DSN" chosen instead of "Use Connection String".  I dont know why this changed from where I left it.  But maybe that doesnt matter?

    -When I open the DSN in excel, I seem to have no problem getting a list of the tables and actual data.   I tried looking at an odc file created by excel but am not sure what I can conclude from it.  Besides, it seems that provider MSADSQL (the one referenced in the odc file) and ssis dont get along, so maybe I shouldnt pin too many hopes on the contents of the odc file.

    -The connection string that was generated by ssis and succeeded when I hit "test connection" originally looks like
    Dsn=some dsn with a dot in the middle of the name;uid=someuser;srv=someserver;dbq=same name as dsn  

    -When I reopen the connection manager and try connecting with "Use User or System DSN" (which it seems to always go back to) , I get an error.
    -when I click "Use Connection String" I get an error.
    -when I do the latter after typing the password in what now seems to be blank  , my connection succeeds.

    -The connection string in the manager's properties seems to have a semicolon on the end whereas the one in its editor does not.
    Otherwise they are the same.

    -In the manager's properties (not conn string), the data source id, initial catalog and server name are blank.

    -Protection Level on the pkg is currently at encryptsensitivewithuserkey .

    -I added a config file with all connection manager's properties clicked.  I dont know what a value of 1 means on Protection Level in this config list.  I typed the pswd into the config file (it has a separate tag) but this didnt help.  Configs are enabled.   There is only one.  I've also tried typing Pwd= or Password= in the connection string of this config file. 

    -vs is version 9.0.30729.1 with no sp # before letters sp.
    -.net version is 3.5 sp1.

    -I see a datareader dest in my toolbox but no datareader source.  I believe that's suposed to happen in Katmai and for situations like mine we are supposed to use the ado.net source instead..

    -in the dtsx file itself I see Protection and Sensitive = "1" in the connection manager's properties

    -if I enter a table name in the properties of the DF ([ADO NET SOURCE].[TableOrViewName]), and turn validateMetaData off (I think) in the ado net source, I am able to see mappings in the advanced editor of the source component, the red x disappears from the source component, but remains on the DF (something about security)....and then add an oledb dest to the df and then debug, I get an error presumably due to security.  Doesnt the config play a role in debugging? 

    -it looks like the password was missing from the config so I added it back.  I delayed validation for grins but I still go down.  When I run I get error 0x80131937 and something about "invalid user authorization".     

    -here is some info from an odbc trace I ran while debugging...

    DIAG [28000] [IBM][UniData ODBC Driver]Invalid user authorization specification. (0)

      DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)

      DIAG [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr). (0)

      DIAG [08004] [IBM][UniData ODBC Driver]Missing '=' in connection string. (0)

    • Edited by db042188 Friday, April 16, 2010 5:14 PM clarity, if that's even possible
    Wednesday, April 14, 2010 7:23 PM

Answers

  • This error message is important "The driver doesn't support the version of ODBC behavior that the application requested".

    Your problem is not SSIS, but the buggy IBM driver you have on your machine. Contact IBM or search for a newer driver.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by db042188 Monday, April 19, 2010 2:48 PM
    Thursday, April 15, 2010 1:57 PM
  • Queries don't have passwords stored against them.

    You should be able to:

    1. With no experience in SSIS - but sufficient experience in .Net - be able to use a Script Task to connect to any data source you like and retrieve any information you like, without using the "managed connections" of SSIS.

    2. You should be able to use a managed connection inside a Script to initiate a connection to your source by using the AcquireConnection method of the Connection Manager.

    If the ADO.Net source can't retrieve a list of table names, then that's because what Ivan (CozyRoc) said is true - the provider isn't supporting (properly) the expected API that ADO.Net providers should (according to the SSIS devs).

    Even given that - you shouldn't use the "table dropdown".  It's convenient, sure - but it makes your package inefficient.  Use an explicit SELECT statement instead that only selects the columns you're going to use - even if that's all of the columns in the table.  Don't use SELECT *.  Spell out the column names specifically.


    Todd McDermid's BlogTalk to me now on
    • Marked as answer by db042188 Monday, April 19, 2010 2:50 PM
    Friday, April 16, 2010 9:29 PM
  • what our new guy did led to a solution.  By asking the source component to invoke its query builder, he was given an option to add new tables or queries after entering a password.

    there was nothing in the table list but plenty to choose from in the VIEW list.  And the view owners were all = to the uid we've been using instead of "dbo".  I didnt know either of the latter but am not convinced I would have succeeded (discovered this) anyway without taking the route he did.  Anyway, the query generated didnt exactly work when I went to preview.   Instead of generating select * from "uid.viewname", it generated something like select uid.i think db name.viewname.* from uid.viewname.  I changed the first part to select * and everything started working. 

    The package seems to be running in debugger.  I'm worried that when we migrate, if it doesnt run with my credentials, that it will abort.  I'm not sure credentials can be proxied like that at run time.  The little bit of reading I did on this part of ssis suggests that unless I switch over to "encrypt with password" (instead of user key), this will be an issue.  And then who knows what other adventures I'll encounter.

    Interestingly (I'll post this separately), when I ran, one experimental column I'm transforming from uni to non uni code created a bit of a challenge.  I got syntax errors trying cast and convert on the select so started using the derived column transform.  It seemed to force me to use a new column name instead of the old.  When I ran , I got a transform error on that column at the source.  So I tried redirecting rows and it worked.  But no rows were redirected.  That worries me.  When  tried "ignore failure"  on that column and removed the error output, the pkg aborted again.  I suppose I'll open this thing in excel and see if the row count matches what ssis loaded.

    If nobody beats me to it, I'll wait a little to read what Ivan, Todd and others have to say before marking somebody's post as an answer.      

    • Marked as answer by db042188 Monday, April 19, 2010 2:51 PM
    Saturday, April 17, 2010 4:34 PM

All replies

  • This error message is important "The driver doesn't support the version of ODBC behavior that the application requested".

    Your problem is not SSIS, but the buggy IBM driver you have on your machine. Contact IBM or search for a newer driver.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by db042188 Monday, April 19, 2010 2:48 PM
    Thursday, April 15, 2010 1:57 PM
  • thanks CozyRoc.  Are you familiar enough with this to explain why excel can use it if its buggy?  How is the password normally passed to "non buggy" odbc connections made thru ado.net if ssis doesnt store it? 

    I did try getting to it from .net (script component as datasource) using the odbc classes.  I figured that I'd need to then pass the password in the connection string.  I used the format Password= and Pwd =.  I even tried Extended Properties with Pwd=.  I still get security issues even though the conn mgr originally cited can connect to it. 

    • Edited by db042188 Thursday, April 15, 2010 5:53 PM update
    Thursday, April 15, 2010 5:42 PM
  • thanks CozyRoc.  Are you familiar enough with this to explain why excel can use it if its buggy?  How is the password normally passed to "non buggy" odbc connections made thru ado.net if ssis doesnt store it? 

    I did try getting to it from .net (script component as datasource) using the odbc classes.  I figured that I'd need to then pass the password in the connection string.  I used the format Password= and Pwd =.  I even tried Extended Properties with Pwd=.  I still get security issues even though the conn mgr originally cited can connect to it. 


    The statement about SSIS "not storing passwords" is incorrect. It does store it, but you cannot access from outside and this is made on purpose for security purposes. So if you are trying to get the password from the existing connection manager, then NO you cannot do it this way. You have to find other means to store and use the password in the script component.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Thursday, April 15, 2010 8:48 PM
  • thanks CozyRoc.  I know the password.  I should have full control when connecting from a .net script component, right?  Yet when I pass the password in the connection string (Pwd=,Password= etc etc) from .net I get a security error.  I'm instantiating my own connection in .net.  I control the connection string.

    So I guess I'm asking not what ssis passes as the password from the connection manager (which connects when I "test connection") but how.  If I understood it, I could probably simulate it in .net, right?  Wouldnt the simulation be done via the connection string I'm using in the .net program?  Or should I be setting some property (in my code) with the password?  Maybe I have to pass it encrypted.    

    Friday, April 16, 2010 2:23 AM
  • we're not sure yet, but a person inexperienced with ssis went in and tried this.  He took a different route and may have found the answer.  I was on the phone with him listening to a play by play while he was doing this.

    Instead of relying on ssis for a table dropdown in the ado.net source component, and instead of typing his query right into the textbox where queries are entered when "from sql command" is chosen, he asked the query builder (in ado.net source's editor)  to assist in building a sql command.  Upon exit, he was asked for a password once more.  And was able to preview the data.  On the surface, it sounds to me like that is further than I got.

    I'm not an expert and we still need to get past a few errors (we havent had a successful run yet) before getting too excited,  but we think that if this is the solution, maybe a password is (and must be) attached to the query also/itself, thus allowing the package to run.  At one point in this whole adventure, I did look at an excel odc file and got the impression that a password is associated with a query. 

    Our newbie wasnt clear on whether he was running with debugger or not.  I have to muddle through this kind of stuff and come to some kind of conclusion.  I'll post the results here.  

    Friday, April 16, 2010 9:22 PM
  • Queries don't have passwords stored against them.

    You should be able to:

    1. With no experience in SSIS - but sufficient experience in .Net - be able to use a Script Task to connect to any data source you like and retrieve any information you like, without using the "managed connections" of SSIS.

    2. You should be able to use a managed connection inside a Script to initiate a connection to your source by using the AcquireConnection method of the Connection Manager.

    If the ADO.Net source can't retrieve a list of table names, then that's because what Ivan (CozyRoc) said is true - the provider isn't supporting (properly) the expected API that ADO.Net providers should (according to the SSIS devs).

    Even given that - you shouldn't use the "table dropdown".  It's convenient, sure - but it makes your package inefficient.  Use an explicit SELECT statement instead that only selects the columns you're going to use - even if that's all of the columns in the table.  Don't use SELECT *.  Spell out the column names specifically.


    Todd McDermid's BlogTalk to me now on
    • Marked as answer by db042188 Monday, April 19, 2010 2:50 PM
    Friday, April 16, 2010 9:29 PM
  • thanks Todd.  If queries dont have passwords glued to them like you say, and my head is somewhat clear at this juncture, I suspect that 1) either debugger is the problem (for us) or 2) once we get past our data type errors we'll find we're still at square one.   I'll post the results here some time over the weekend. 
    Friday, April 16, 2010 9:38 PM
  • The only reason you've seen passwords in Excel queries and the like is that Excel chooses to store the connection and query in the same "package" - a "data source" - so that it can be easily consumed by it at runtime.  It's not because that's how ADO needs it packaged.  In fact, Excel doesn't use ADO to connect (I don't think)...
    Todd McDermid's BlogTalk to me now on
    Friday, April 16, 2010 9:48 PM
  • dont know anything about this area of the technology, but the provider MSADSQL I saw referenced in the odc file isnt compatible (I dont think) with anything I tried in ssis. 

    I tried using a variation of the connection string I saw in the odc file and also in an ssis pkg provided to me by somebody who simply tried calling old dts from ssis on this very same connection, both with MSADSQL referenced.  The error messages suggested that MSADSQL (whatever that is) is a no no in ssis's connection world.  But even so, I saw some similarities between the connection string there and the one generated by ssis, so I thought maybe some things carried over.

    I should add that part of me wants to investigate sticking an excel source into the pkg but I dont remember an option that would get it to refresh first via ssis.  But I think there is an excel option in some interfaces to refresh on open. 

    The down side is that it is probably very slow especially for large files.   The up side is that if I never get the vendor or MS (from its knowledge base) to give us a clue (including info on how to do my own instantiation from within framework 3.5 itself) , I'd probably still be highly motivated  to lose old dts in favor of a slower data source. 

    • Edited by db042188 Saturday, April 17, 2010 1:44 PM update
    Friday, April 16, 2010 10:18 PM
  • what our new guy did led to a solution.  By asking the source component to invoke its query builder, he was given an option to add new tables or queries after entering a password.

    there was nothing in the table list but plenty to choose from in the VIEW list.  And the view owners were all = to the uid we've been using instead of "dbo".  I didnt know either of the latter but am not convinced I would have succeeded (discovered this) anyway without taking the route he did.  Anyway, the query generated didnt exactly work when I went to preview.   Instead of generating select * from "uid.viewname", it generated something like select uid.i think db name.viewname.* from uid.viewname.  I changed the first part to select * and everything started working. 

    The package seems to be running in debugger.  I'm worried that when we migrate, if it doesnt run with my credentials, that it will abort.  I'm not sure credentials can be proxied like that at run time.  The little bit of reading I did on this part of ssis suggests that unless I switch over to "encrypt with password" (instead of user key), this will be an issue.  And then who knows what other adventures I'll encounter.

    Interestingly (I'll post this separately), when I ran, one experimental column I'm transforming from uni to non uni code created a bit of a challenge.  I got syntax errors trying cast and convert on the select so started using the derived column transform.  It seemed to force me to use a new column name instead of the old.  When I ran , I got a transform error on that column at the source.  So I tried redirecting rows and it worked.  But no rows were redirected.  That worries me.  When  tried "ignore failure"  on that column and removed the error output, the pkg aborted again.  I suppose I'll open this thing in excel and see if the row count matches what ssis loaded.

    If nobody beats me to it, I'll wait a little to read what Ivan, Todd and others have to say before marking somebody's post as an answer.      

    • Marked as answer by db042188 Monday, April 19, 2010 2:51 PM
    Saturday, April 17, 2010 4:34 PM
  • We can work through your conversion issue in the other thread you started.  But to finish off this one, you have concerns over credentials we can address.

    If you are using "integrated security" - and by that I mean that your provider captures the credentials of the user executing the commands, rather than you explicitly passing a user and password combination - then you'll need to look at using Proxies to execute your package.  However, I think I remember you referring to building your connection string with a user name and password.  If that's the case, the credentials you run under should not affect this part of your package.  (It will affect the ProtectionLevel, and perhaps some other connection managers if they use integrated security.)


    Todd McDermid's Blog Talk to me now on
    Saturday, April 17, 2010 6:31 PM
  • thanks Todd.  Please tell us if you still believe this odbc connection is non compliant and/or buggy.  Then I can mark which replies are valid answers.  Or just mark the replies you think are answers.
    Saturday, April 17, 2010 6:43 PM
  • I'm no expert at those ODBC drivers - Ivan would know better, I think.  But from what you've described, and what Ivan's said, I think that the ODBC driver you've got isn't fully compliant.  You may get some info about it if you post in one of the Data Platform Development forums.  Even though you're not asking about developing a provider yourself, you'll probably find experts there that have experience with, and an educated opinion about the provider you have.
    Todd McDermid's Blog Talk to me now on
    Saturday, April 17, 2010 7:20 PM
  • lots of reads at the provider forum on the question regarding compliance but no answers.  We've seen some weird behavior with this connection so I'm going to assume it is non compliant in spite of the fact that there was a workaround (actually two workarounds, one using build query and the other redirecting even though nothing got redirected) using ssis. 
    Monday, April 19, 2010 2:48 PM
  • this odbc driver is level 2 compliant.  The expert at the other forum says that is generally good but not necessarily for UIs like ssis. 
    Tuesday, April 20, 2010 3:23 PM