Post reply

Import a .xls file to load into SQL database Via SSIS package

  • April 11, 2016 at 5:39 am

    #400004

    Microsoft Visual Studio 2008 (version 9)

    I created a SSIS package were I import a .xls file to load into SQL database.

    So I created a connection with the following properties:

    (connectionString : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\fld6Filer\BRDProduction_SurveyFiles$\Incoming\TestingSurveyFiles\MyleneTestingFiles\CriticalList\CriticalList.xls;Extended Properties="EXCEL 8.0;HDR=YES";)

    So far so good, everything is working as planned!

    However, I want to make that connection dynamic by reading the value from a sql table and assigning to to the ConnectionString. So I added the following lines of code in a script task:

    String InputFileName = Dts.Variables["FileName"].Value.ToString();

    Dts.Connections["CriticalListExcelFile"].ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + InputFileName + ";Extended Properties=\"" + "EXCEL 08.0;HDR=YES" + "\"" + ";";

    The value is being passed without any problem. But when I get to the part in the data flow where I actually import the file, I get the following errors:

    [Source - Excel File [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "CriticalListExcelFile" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: component "Source - Excel File" (1) failed validation and returned error code 0xC020801C.

    [SSIS.Pipeline] Error: One or more component failed validation.

    Error: There were errors during task validation.

    Please note that I do have the DelayValidation set to True.

    Can somebody help me with that? I did found some tips on the web like changing the Run64BitRuntime to False under the debug options… but I can’t find where to go!! Help!!!

  • April 12, 2016 at 3:15 pm

    #1871553

    I think you're correct - you need to use the 32 bit runtime in order to import xls files. During testing this can be set in Visual Studio under Project->[project name] properties->Debugging->Run64BitRuntime needs to be set to false. After creating a job to run your project there is also a job property called Execution options that has a checkbox to Use 32 bit runtime. Hope that helps.

  • April 13, 2016 at 12:27 pm

    #1871837

    I did changed the Run64BitRuntime option to False. Nope!!! Still the same error 🙁

    Any other idea what it can be?

  • April 15, 2016 at 2:30 am

    #1872243

    First off you should look into using the Microsoft ACE (Microsoft Access Database Engine 2010 (or 2013) Redistributable) for reading the excel files as it does allow for a 64 bit runtime.

  • April 15, 2016 at 2:44 am

    #1872246

    Can you get the string value out for your dynamic connection and share it here?

    Does the package fail if you hardcode the "FileName" variable using your tested file?

    LinkedIn

  • April 16, 2016 at 5:45 am

    #1872555

    Unfortunatly, I work for the federal government. We cannot use whatever software of version we want here 🙁

  • April 16, 2016 at 5:46 am

    #1872556

    Yes. When the filename is hear coded... it works!

    I will send you the value of "filename" monday morning from work. Thank you 🙂

  • April 16, 2016 at 10:21 am

    #1872564

    mylenechalut (4/16/2016)

    Unfortunatly, I work for the federal government. We cannot use whatever software of version we want here 🙁

    That does not invalidate the fact that you should use it - and as it is a Microsoft supplied application, no licensing required, I would make a case with your manager to get this installed.

    regarding your issue try and create a package as follows.

    New Variable type string - name ExcelFile

    new variable type object - name it ExcelFileList

    execute sql task - retrieve files to process from sql server and assign output to variable ExcelFileList

    Foreach loop container - set collection to a foreach ADO enumerator and assign source to variable ExcelFileList, set variable mappings variable ExcelFile to index 0

    dataflow - read from excel file connection and add to desired destination

    On the excel file connection set expression "connection string" to "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[ExcelFile] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

    This assumes that the files to process on sql server contain full path to the file, not just the filename

Viewing 8 posts - 1 through 7 (of 7 total)

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