Importing Excel Files into Tables

  • I have been able to successfully import txt and csv files in a folder using a ForEach Loop, but when I use the same method to import excel files I get an error in the Excel Source saying the "Excel Connection Manager" failed.

    Here are the settings I'm using for the excel file import:

    Control Flow contains the following:

    Foreach Loop Container

    Folder = C:\test

    Files = *excelfile*.xls

    Fully Qualified

    Variable Mappings = User::Variable (this is simply a string data type with no value going to be used in the connection string)

    Data Flow Task inside the ForEach Loop

    Data Flow Task contains the following:

    Excel Source

    Connection Manager -

    OLE DB connection manager = Excel Connection Manager (this is a "New" connection with the Excel file path pointing to one of the excel files in the C:\test folder using the "Browse" button)

    Data access mode = table or view

    Name of the Excel sheet = Sheet1

    Columns - would be all the columns in the sheet1 of the excel file in the connection manager

    This doesn't give me an error, but I need the User::Variable to be the connection string in order for the ForEach Loop to capture all the files in the folder so I go into the Excel Connection Manager in the Connection Managers, go into Expressions and in Property I chose ConnectionString with the User::Variable in the Expression. After I hit ok, I get the "Excel Connection Manager" failed error message and the ExcelFilePath in the connection manager is now empty. The properties in the Excel Connection Manager are:

    ConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;

    DelayValidation: False

    ExcelVersionNumber:3 (this is grayed out)

    Expressions: ConnectionString = @[User::Variable]

    FirstRowHasColumnName=True

    Name: Excel Connection Manager

    Password: ******** (I don't know what this password is, it was there when I created the connection manager)

    RetainSameConnection: False

    Is there any step or something I'm missing in order to get the connection manager to work?

    Thanks.

  • mrtrich99 (2/5/2009)


    I have been able to successfully import txt and csv files in a folder using a ForEach Loop, but when I use the same method to import excel files I get an error in the Excel Source saying the "Excel Connection Manager" failed.

    Here are the settings I'm using for the excel file import:

    Control Flow contains the following:

    Foreach Loop Container

    Folder = C:\test

    Files = *excelfile*.xls

    Fully Qualified

    Variable Mappings = User::Variable (this is simply a string data type with no value going to be used in the connection string)

    Data Flow Task inside the ForEach Loop

    Data Flow Task contains the following:

    Excel Source

    Connection Manager -

    OLE DB connection manager = Excel Connection Manager (this is a "New" connection with the Excel file path pointing to one of the excel files in the C:\test folder using the "Browse" button)

    Data access mode = table or view

    Name of the Excel sheet = Sheet1

    Columns - would be all the columns in the sheet1 of the excel file in the connection manager

    This doesn't give me an error, but I need the User::Variable to be the connection string in order for the ForEach Loop to capture all the files in the folder so I go into the Excel Connection Manager in the Connection Managers, go into Expressions and in Property I chose ConnectionString with the User::Variable in the Expression. After I hit ok, I get the "Excel Connection Manager" failed error message and the ExcelFilePath in the connection manager is now empty. The properties in the Excel Connection Manager are:

    ConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;

    DelayValidation: False

    ExcelVersionNumber:3 (this is grayed out)

    Expressions: ConnectionString = @[User::Variable]

    FirstRowHasColumnName=True

    Name: Excel Connection Manager

    Password: ******** (I don't know what this password is, it was there when I created the connection manager)

    RetainSameConnection: False

    Is there any step or something I'm missing in order to get the connection manager to work?

    Thanks.

    Can you make a simple test? Create sample SSIS package, which imports only one excel file. Also can you please send the complete error you get?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • The attached the "ExcelError" file is the error code I get if I only have the User::Variable as the Connection String in the expression.

    When I don't use the expression for the connection string, it works, but I only get the first file loaded because the connection string is not using the variable in the foreach loop that captures each file it's on.

    I even tried the following Connection String in the expression:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\text\\" + @[User::Variable] + ";Extended Properties=Excel 8.0;HDR=YES;"

    and the "ExcelError2" file is the error code I get. The properties for this is in the "ExcelConnectionProperties" file.

    Am I missing something in the Properties?

  • Try using a csv file, this will work, or if you are using a excel sheet, then try with only one sheet and delete other sheets, this might work 🙂

  • that test.xls file does have only 1 sheet. i thought that was the problem too, but it gives me the same error as files with multiple sheets.

    I want to automate this so I don't want to have to deal with taking the file and saving it as a csv file. also, the server i'm working in does not have excel installed so I don't have any way of opening the file and saving as a csv file.

  • mrtrich99 (2/9/2009)


    The attached the "ExcelError" file is the error code I get if I only have the User::Variable as the Connection String in the expression.

    When I don't use the expression for the connection string, it works, but I only get the first file loaded because the connection string is not using the variable in the foreach loop that captures each file it's on.

    I even tried the following Connection String in the expression:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\text\\" + @[User::Variable] + ";Extended Properties=Excel 8.0;HDR=YES;"

    and the "ExcelError2" file is the error code I get. The properties for this is in the "ExcelConnectionProperties" file.

    Am I missing something in the Properties?

    It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    I did that and I got the following error in the attached files.

  • mrtrich99 (2/9/2009)


    It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    I did that and I got the following error in the attached files.

    This problem is not related to the original issue I think. You have some problems with your Visual Studio integration. Can you try to execute from outside Visual Studio?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • CozyRoc (2/9/2009)

    It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    Hi Cozy,

    I believe you are correct when you say that the data flow task is validating during the setup execution because when I setup the connection with a direct connection to the file, the connection works (the attached files shows the good connection). But once I insert the variable in the connectionstring in the expression, it bombs out right away. Is there another property other than the DelayValidation I can change the value to so the data flow task doesn't get validated during setup?

  • mrtrich99 (2/9/2009)


    CozyRoc (2/9/2009)

    It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    Hi Cozy,

    I believe you are correct when you say that the data flow task is validating during the setup execution because when I setup the connection with a direct connection to the file, the connection works (the attached files shows the good connection). But once I insert the variable in the connectionstring in the expression, it bombs out right away. Is there another property other than the DelayValidation I can change the value to so the data flow task doesn't get validated during setup?

    I tried using the Execute Package by opening the dtsx file and same error message. I've attached my dtsx file along with the excel file i'm using to test. If anyone can give this a shot to see if they get an error I would greatly appreciate it. It might just as well be the way BID was setup on my server.

    Thanks.

  • mrtrich99 (2/9/2009)


    mrtrich99 (2/9/2009)


    CozyRoc (2/9/2009)

    It fails because it tries to validate the data flow task, which will be setup during execution. Right-click on your data flow task and change DelayValidation property to true. Give it a try again and let us know how it goes.

    Hi Cozy,

    I believe you are correct when you say that the data flow task is validating during the setup execution because when I setup the connection with a direct connection to the file, the connection works (the attached files shows the good connection). But once I insert the variable in the connectionstring in the expression, it bombs out right away. Is there another property other than the DelayValidation I can change the value to so the data flow task doesn't get validated during setup?

    I tried using the Execute Package by opening the dtsx file and same error message. I've attached my dtsx file along with the excel file i'm using to test. If anyone can give this a shot to see if they get an error I would greatly appreciate it. It might just as well be the way BID was setup on my server.

    Thanks.

    I've made it to work. I'm attaching the sample package. I think there was something wrong with ConnectionString and expression you have used. Give it a try.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • Hi Cozy,

    Thanks for doing that, I wish I could buy you a couple beers right about now. Now I see how the variable is used in the expression.

    What I had was:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test" + @[User::Variable] + ";Extended Properties=Excel 8.0;HDR=YES;"

    and what made it work was:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Variable] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

    Again, thanks so much for the help.

Viewing 12 posts - 1 through 11 (of 11 total)

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