locked
microsoft jet 4.0 oledb provider RRS feed

  • Question

  • hi,

    i  need to use,following provider ,

    native oledb\microsoft jet 4.0 oledb provider.

    now here in connection string, i have date source = c:\users:\.txt

    i need to make this dynamic,like need to give variable name,so whatever file comes ,it wil pick it up

    if i put data source -user::file1

    it give sme error not valid file format.

    i cannot use connection string,because it involves other things

    please help me

    Thursday, November 15, 2012 5:46 PM

Answers

  • Hi tsql_new

    It looks like you variable file1 contains just the file path.

    Please try to set ExcelFilePath parameter for the connection string parameter and not the whole connection string. Remember to set the EvaluateAsExpression property to true for the variable.


    Pérez

    • Proposed as answer by Eileen Zhao Tuesday, November 20, 2012 9:05 AM
    • Unproposed as answer by Eileen Zhao Thursday, November 22, 2012 7:04 AM
    • Marked as answer by Eileen Zhao Thursday, November 22, 2012 8:20 AM
    Thursday, November 15, 2012 9:23 PM

All replies

  • Hi tsql_new

    Try creating the connection string the first time with the excel file. After this use the same data source string but put this in a variable. At this point the package should still work. Now you should be able to dynamically change this variable. If the excel wont exist at the beginning of the package start make sure to turn off validation for this task. (When creating a package never turn off validation of tasks! unless for example you know the reason) In this case you know the package works but are aware that at the start of its execution it shouldn't expect to find this file.


    Pérez

    Thursday, November 15, 2012 6:24 PM
  • hi

    thansk for your reply,

    i need more clarification.

    i have .dat file, which has connection string

    Data Source=C:\Users\Desktop\DEA\n2.dat;Provider=Microsoft.Jet.OLEDB.4.0;

    now if i go to expression->conncetion string - user::file1

    now file1 has just path for C:\Users\Desktop\DEA\n2.dat,

    so its not working.

    if i put Data Source=user::file1;Provider=Microsoft.Jet.OLEDB.4.0;

    gives me error like not valid format

    please help me

    Thursday, November 15, 2012 6:37 PM
  • You need to create a package variable where you build your new connection string. Set the variable's Evauluate as Expression to True. Then on the connection manager, refer to the variable in question for the connection string.

    MCITP:DBA, MCDBA, MCSA

    Thursday, November 15, 2012 6:43 PM
  • Hi tsql_new

    Hi please try: http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/

    There might be another article which is better but I truely searched for about 1 minute on this. I was going to write it all out from my brain but if it is already there I figured I'd give you a link.


    Pérez

    Thursday, November 15, 2012 6:52 PM
  • i do knwo how to make falt file source dynamic

    but here its jet provider.

    please ss the image below,

    the third one is where i am not using any variable.i cannot put just connectionstring -variable.

    coz actual connection string has data source(which has file path) and some other things

    i am using oledb source,in which native oledb\microsoft 4.0 jet oledb provider.

    Thursday, November 15, 2012 7:02 PM
  • third image

    Thursday, November 15, 2012 7:02 PM
  • Hi tsql_new

    It looks like you variable file1 contains just the file path.

    Please try to set ExcelFilePath parameter for the connection string parameter and not the whole connection string. Remember to set the EvaluateAsExpression property to true for the variable.


    Pérez

    • Proposed as answer by Eileen Zhao Tuesday, November 20, 2012 9:05 AM
    • Unproposed as answer by Eileen Zhao Thursday, November 22, 2012 7:04 AM
    • Marked as answer by Eileen Zhao Thursday, November 22, 2012 8:20 AM
    Thursday, November 15, 2012 9:23 PM
  • tsql_new,

    Can you please tell us what expression you're using to set your variable?

    If you don't know, please give us two bits of information. 1) The current path and filename (which I assume is C:\Users\Desktop\DEA\n2.dat) and 2) the new path and filename.

    With these two bits of information we can help you write your expression if you're having trouble.


    MCITP:DBA, MCDBA, MCSA

    Tuesday, November 20, 2012 11:31 AM