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

Question

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

mylenechalut

SSC-Addicted

Points: 406
More actions
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!!!

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

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

strinkner SSC Enthusiast Points: 122 More actions · Answer 1

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.

mylenechalut SSC-Addicted Points: 406 More actions · Answer 2

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

Any other idea what it can be?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

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.

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 4

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?

mylenechalut SSC-Addicted Points: 406 More actions · Answer 5

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

mylenechalut SSC-Addicted Points: 406 More actions · Answer 6

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

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 7

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