I get this error when I try to connect to any excel through MSSQL Server Data Import i.e. SSIS package The `Microsoft.ACE.OLEDB.16.0` provider is not registered on the local machine. (`System.Data`) 

It is not the same version I think some other patching is needed

&#39;Microsoft.ACE.OLEDB.16.0&#39; provider is not registered on the local machine. (System.Data)

As a .NET developer, for what reasons should I prefer SSIS packages over writing code? We have *a ton* of packages in production where I currently work, and they&#39;re a nightmare to both &quot;write&quot; (perhaps draw?) and maintain. Each package looks like a bowl of multicolored spaghetti with C# and VB.NET scripts mixed in at the points where the abstractions break down. To figure out what each &quot;Execute SQL Task&quot; or &quot;Foreach Loop&quot; does, I have to double click the damned thing and browse through a tree of literal values and expressions, scattered across multiple tabs.

I&#39;m open minded, so I&#39;d like to know if any other *good developers* find SSIS more productive than just writing some code. If you do find SSIS more productive, please tell me why.

Should programmers use SSIS, and if so, why?

I have a SSIS package that eventually I would like to pass parameters too, these parameters will come from a .NET application (VB or C#) so I was curious if anyone knows of how to do this, or better yet a website with helpful hints on how to do it. 

So basically I want to execute a SSIS package from .NET passing the SSIS package parameters that it can use within it.  

For instance, the SSIS package will use flat file importing into a SQL db however the Path and name of the file could be the parameter that is passed from the .Net application.


How to execute an SSIS package from .NET?

I am using SQL Server 2008 import and export wizard. I need to import a database. I opened the SQL server import/export wizard and I went through the following actions:-

1. for the destination I chose &quot;SQL server native client 10&quot;.

2. then I selected copy data from one or more tables or view.

3. SSIS run immediately

but i got the following errors, 

&gt; Operation stopped...
&gt; 
&gt; - Initializing Data Flow Task (Success)
&gt; 
&gt; 
&gt; - Initializing Connections (Success)
&gt; - Setting SQL Command (Success)
&gt; 
&gt; 
&gt; - Setting Source Connection (Success)
&gt; 
&gt; 
&gt; - Setting Destination Connection (Success)
&gt; 
&gt; 
&gt; - Validating (Error)
&gt; 
&gt; Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into
&gt; the read-only column &quot;ActionID&quot;.  (SQL Server Import and Export
&gt; Wizard)  
&gt; 
&gt; Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
&gt; (SQL Server Import and Export Wizard)  
&gt; 
&gt; Error 0xc004706b: Data Flow Task 1: &quot;component &quot;Destination -
&gt; AuditActions&quot; (22)&quot; failed validation and returned validation status
&gt; &quot;VS_ISBROKEN&quot;.  (SQL Server Import and Export Wizard)

It seems that I can not import identity columns and timestamps columns, so how I can force these values to be imported?

Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column

I have a project in SSIS and I&#39;ve added an Execute SQL Task which sends its result out to a variable. I wanted to confirm the value because I was worried that it would try to write it out as a resultset object rather than an actual integer (in this case I&#39;m returning a COUNT).

My first thought was just to run it in debug mode and add the global variable to my Watch window. Unfortunately, when I right-click on the Watch window, the option to &quot;Add Variable&quot; is greyed out. What am I missing here?

I&#39;ve gotten around confirming that my variable is set correctly, so I&#39;m not interested in methods like putting a script in to do a MsgBox with the value or anything like that. For future reference I&#39;d like to be able to watch variables in debug mode. If there are some kind of constraints on that then I&#39;d like to know the what and why of it all if anyone knows.

The help is woefully inadequate on this one and every &quot;tutorial&quot; that I can find just says, &quot;Add the variable to the Watch window and debug&quot; as though there should never be a problem doing that.

Thanks for any insight!

Watching variables in SSIS during debug

Argh! I created an SSIS package via an Import Wizard and I can&#39;t find the SSIS packages on the server using Management Studio. Execute an SSIS package doesn&#39;t appear as an option when I go into job scheduler, either.



How do I view the SSIS packages in SQL Server Management Studio?

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet in the Excel workbook, the SQL is in the excel connection manager (effectively a create table statement that creates a sheet) and is derived from the mapping of the columns from the DB.

No matter what I have done I keep getting this unicode --&gt; non-unicode conversion error between my source and destination. Tried conversion to string[DT_STR] between S &gt; D, removed it, changed SQL Table VARCHAR to NVARCHAR and still get this flippin error. 

Because I am creating the sheet in Excel with a SQL statement I do not see any way to actually pre-define what the data types of the columns will be in the Excel sheet. I imagine it would be a default meta data but I do not know.

So between my SQL table destination and the creation of my Excel sheet with this SSIS sql statement how can I stop this error coming up?

My error is:

&gt; Error at Data Flow Task [OLE DB Source [1]]: Column &quot;MyColumn&quot; cannot convert between unicode and non-unicode string data types.

And for all nvarchar columns.


Appreciate any help

Thanks

Andrew



SSIS Convert Between Unicode and Non-Unicode Error

Can someone tell me what the SSIS package is all about?

I am totally new to data-warehousing and I am going crazy with the terms SSIS, DTL, ETL and so on. I am looking for a big picture and how it fits in the overall .NET/SQL Server architecture.

What is the SSIS package and what does it do?

I want to use SSIS /BIDS project in Visual Studio 2012. 

I have both Visual Studio 2010 and Visual Studio 2012 installed along with Microsoft SQL 
Server 2012. 

VS 2010 was installed first followed by MS SQL 2012 which got me BIDS integrated with VS 2010. 

However I cannot seem to find any help on integrating BIDS with VS 2012.

I came across few question on SO related to this topic but all of them had integration with 2010 and not 2012. 

I know that with MS SQL 2012 I get a SQL Server Data Tool. Does this mean that Visual Studio 2012 and BIDS cannot be integrated ?



**Solution 2012:**

I had to update the solution here since one of the answers I received partially solved the issue. The [Answer][1] provided by did solve the issue @user2141936 but I kept on getting an BlockMixedArchitectureInstall error while updating an existing instance.

It worked without  with creating a new instance!!


  [1]: https://stackoverflow.com/a/15258988/314763

**Side Note** :
Installing the update broken my publishing database project onto 2012 server. You need to download and install a DACFramework patch for it.

**Solution 2013:** 

download and install http://www.microsoft.com/en-us/download/details.aspx?id=42313

Using SSIS BIDS with Visual Studio 2012 / 2013

I have an excel file with four text columns:
one of them is called ShortDescription which has the longest value.
I created a table in SQL Server 2008 database, with four columns and the ShortDescription column type is set to NvarChar(Max).

but when using the SSIS import and export dialog, I keep getting the mentioned error in the title, even when I set the OnTruncation option to Ignore.

I tried to clear the column data, and it succeeded (so I made sure that the problem is in the ShortDescription column). 
I tried to copy the whole data  to another excel work book, and still no luck.

any ideas ???



Text was truncated or one or more characters had no match in the target code page When importing from Excel file

If I have the option of using `.Net` and can do data `transformations` just fine in .Net, when would I need `SSIS`?  Is there a certain task that `SSIS` would be better for?  Are the added benefits of transparency worth it?  Is it just what I am more comfortable with?  What are the best practices for determining this?

.Net vs SSIS: What should SSIS be used for?

I have made a dtsx package on my computer using SQL Server 2008.  It imports data from a semicolon delimited csv file into a table where all of the field types are NVARCHAR MAX.

It works on my computer, but it needs to run on the clients server.  Whenever they create the same package with the same csv file and destination table, they receive the error above.

We have gone through the creation of the package step by step, and everything seems OK.  The  mappings are all correct, but when they run the package in the last step, they receive this error.  They are using SQL Server 2005.

Can anyone advise where to begin looking for this problem?

Import Package Error - Cannot Convert between Unicode and Non Unicode String Data Type

I&#39;m trying to import a flat file into an oledb target sql server database.

here&#39;s the field that&#39;s giving me trouble:

![enter image description here][1]

here are the properties of that flat file connection, specifically the field:

![enter image description here][2]

here&#39;s the error message:

&gt; [Source - 18942979103_txt [424]] Error: Data conversion failed. The
&gt; data conversion for column &quot;recipient-name&quot; returned status value 4
&gt; and status text &quot;Text was truncated or one or more characters had no
&gt; match in the target code page.&quot;.


What am I doing wrong?


  [1]: http://i.stack.imgur.com/CBYws.png
  [2]: http://i.stack.imgur.com/GTBcj.png

Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

###RFC 4180:

**[RFC 4180][1]** defines *`Common Format and MIME Type for Comma-Separated Values (CSV) Files`*. One of the requirements of the *`RFC 4180`* is stated as below. This is the point *`#7`* in the RFC link.

    If double-quotes are used to enclose fields, then a double-quote
    appearing inside a field must be escaped by preceding it with
    another double quote.  For example:
    
    &quot;aaa&quot;,&quot;b&quot;&quot;bb&quot;,&quot;ccc&quot;

###SQL Server 2000:

**`DTS Export/Import Wizard`** in *`SQL Server 2000`* seems to conform to the above mentioned standards even though the RFC 4180 itself seem to have been published only on *October 2005*. I am using the below stated *SQL Server 2000* version.

    Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
    May  3 2005 23:18:38 
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

###SQL Server 2012:

**`SQL Server Import and Export Wizard`** in *`SQL Server 2012`* does not export the data from table to CSV file according to the standard defined in RFC 4180. I am using the below stated *SQL Server 2012* version.

    Microsoft SQL Server 2012 - 11.0.2316.0 (X64) 
    Apr  6 2012 03:20:55 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)

###Issue Simulation:

Here is a sample that I ran in both *SQL Server 2000* and *SQL Server 2012*. I ran the below query to create a table and insert few records. The `ItemDesc` column has data with double-quotes in it. My intention is to export the data from both these SQL Server versions using their in-built export data wizard and compare the generated CSV files.


    CREATE TABLE dbo.ItemInformation(
    	ItemId nvarchar(20) NOT NULL,
    	ItemDesc nvarchar(100) NOT NULL
    ) 
    GO
    
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100338754&#39;, &#39;Crown Bolt 3/8&quot;-16 x 1&quot; Stainless-Steel Hex Bolt&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;202255836&#39;, &#39;Simpson Strong-Tie 5/8&quot; SSTB Anchot Bolt&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100171631&#39;, &#39;Grip-Rite #11 x 1-1/2&quot; Electro-Galvanized Steel Roofing Nails&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;202210289&#39;, &#39;Crown Bolt 1/2&quot; x 3&quot; &quot;Zinc-Plated&quot; Universal Clevis Pin&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;100136988&#39;, &#39;Tapcon 3/16&quot; x 1-3/4&quot; Climaseal Steel &quot;Flat-Head&quot; Phillips Concrete Anchors (75-Pack)&#39;);
    INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES (&#39;203722101&#39;, &#39;KwikTap 3/16&quot; x 2-1/4&quot; &quot;Flat-Head&quot; Concrete Screws (100-Pack)&#39;);
    GO

On the *`DTS Export/Import Wizard`* in *`SQL Server 2000`*, I used the below settings to export the data to CSV file. I saved the file under the name `SQLServer2000_ItemInformation.csv`.

![DTS Export/Import Wizard][2]

On the *`SQL Server Import and Export Wizard`* in *`SQL Server 2012`*, I used the below settings to export the data to CSV file. I saved the file under the name `SQLServer2012_ItemInformation.csv`.

![SQL Server Import and Export Wizard - Choose a destination][3]

![SQL Server Import and Export Wizard - Configure Flat File Destination][4]

Here is the comparison between the two files using Beyond Compare. The left side contains the file generated by `SQL Server 2000` and the right side contains the file generated by `SQL Server 2012`. You can notice that the left side file from `SQL Server 2000` contains additional double-quotes to compensate the embedded quotes in the data column. This conforms to the standard specified in `RFC 4180` but it is clearly missing from the file generated by `SQL Server 2012`

![File Comparison][5]

###Searches on the web:

I searched for this bug on the web and found the following links. Following are the bug reports on Microsoft Connect. All these issues seem to be related to importing a file but nothing about exporting data. All these bugs have been closed as `Fixed`.

 - [*SSIS flat file parser does not read Column delimiters embedded in text data*][6]
 - [*Flat File Connection Manager not handling Text Delimiters in CSV Files*][7] 
 - [*Embedded quotes in Flat File Import fails*][8]
 - [*BUG: Flat File Connection Manager: multiple-character text qualifier does not load all data*][9]

Below post on MSDN blog states that changes have been made in SQL Server 2012 with respect to `Flat file source supports embedded qualifiers and a variable number of columns per row`

 - [*SSIS - What’s New in SQL Server Denali*][10]

Another post on MSDN blog states the same under the section `Embedded Qualifiers`.

 - [*Flat File Source Changes in Denali*][11]

###Workaround that I know of:

I know a workaround to fix the issue by writing a query that would replace all double-quotes (**`&quot;`**) in my column data with two double-quotes (**`&quot;&quot;`**) so that the exported file will end up with correct embedded qualifier data. This would avoid pulling the data directly from the table as it is.

###My questions:

 - I don&#39;t know if this issue has been truly fixed in `SQL Server 2012`. Has this issue been fixed only for *`importing`* files that have embedded text qualifiers and **`not`** for *`exporting`* data to CSV?

 - Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?

###Microsoft Connect:

I have submitted a bug report on Microsoft Connect website to get their feedback. Here is the link to the bug report. If you agree that this is a bug, please visit the below link to vote up on `Microsoft Connect` website.

[*Embedded text qualifier during export to CSV does not conform to RFC 4180*][12]


  [1]: https://www.rfc-editor.org/rfc/rfc4180
  [2]: http://i.stack.imgur.com/fdFSY.png
  [3]: http://i.stack.imgur.com/toryR.png
  [4]: http://i.stack.imgur.com/MvbUx.png
  [5]: http://i.stack.imgur.com/ofh2w.png
  [6]: https://connect.microsoft.com/SQLServer/feedback/details/282396/ssis-flat-file-parser-does-not-read-column-delimiters-embedded-in-text-data
  [7]: http://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files
  [8]: https://connect.microsoft.com/SQLServer/feedback/details/267529/embedded-quotes-in-flat-file-import-fails
  [9]: https://connect.microsoft.com/SQLServer/feedback/details/540056/bug-flat-file-connection-manager-multiple-character-text-qualifier-does-not-load-all-data
  [10]: http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx?PageIndex=2#comments
  [11]: http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx
  [12]: https://connect.microsoft.com/SQLServer/feedback/details/778980/embedded-text-qualifier-during-export-to-csv-does-not-conform-to-rfc-4180#details

How to fix the embedded text qualifier issue while exporting data to CSV flat file?

I am running an SSIS package which will replace data for a few tables from FlatFiles to existing tables in a database.

My package will truncate the tables and then insert the new data. When I run my SSIS package, I get an exception because of the foreign keys.

Can I disable the constraints, run my import, then re-enable them?

Temporarily disable all foreign key constraints

I have a DTSX file in a project I&#39;m taking over. I have Visual Studio 2005 Pro, but it just opens it as an XML file. SQL Server Management Studio 2005 does the same. 

I&#39;ve seen people opening these files in some workflow-esque format; Business Intelligence Development Studio comes to mind. 

Is this part of Visual Studio or SQL? Does it have to be purchased seperately? Can I open this file in a more useful way with the tools I have?

How do I edit SSIS package files?

I have ssis package in that I&#39;m taking values from flat file and insert it into table.

I have taken one Execute SQL Task in that creating one temptable 

    CREATE TABLE [tempdb].dbo.##temptable 
    (
    date datetime,
    companyname nvarchar(50),
    price decimal(10,0),
    PortfolioId int,
    stype nvarchar(50)
    )

    Insert into [tempdb].dbo.##temptable (date,companyname,price,PortfolioId,stype) 
    SELECT   date,companyname,price,PortfolioId,stype
    FROM        ProgressNAV
    WHERE     (Date = &#39;2011-09-30&#39;) AND (PortfolioId = 5) AND (stype in (&#39;Index&#39;))
    ORDER BY CompanyName

Now in above query I need to pass `(Date = &#39;2011-09-30&#39;) AND (PortfolioId = 5) AND (stype in (&#39;Index&#39;))`
these 3 parameter using variable name I have created variables in package so that I become dynamic.

How to pass variable as a parameter in Execute SQL Task SSIS?

I am trying to import the data from Excel file into SQL Server database. I am unable to do so because I am getting following errors in the log file. Please help. The log erros are as as follows:-


&gt;[OLE DB Destination [42]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available.  Source: &quot;Microsoft SQL Native Client&quot;  Hresult: 0x80040E21  Description: &quot;Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.&quot;. 
&gt;
&gt;[OLE DB Destination [42]] Error: There was an error with input column &quot;Copy of F2&quot; (5164) on input &quot;OLE DB Destination Input&quot; (55). The column status returned was: &quot;The value violated the integrity constraints for the column.&quot;. 
&gt;
&gt;[OLE DB Destination [42]] Error: The &quot;input &quot;OLE DB Destination Input&quot; (55)&quot; failed because error code 0xC020907D occurred, and the error row disposition on &quot;input &quot;OLE DB Destination Input&quot; (55)&quot; specifies failure on error. An error occurred on the specified object of the specified component. 
&gt;
&gt;[DTS.Pipeline] Error: The ProcessInput method on component &quot;OLE DB Destination&quot; (42) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. 
&gt;
&gt;[DTS.Pipeline] Error: Thread &quot;WorkThread0&quot; has exited with error code 0xC0209029. 
&gt;
&gt;[Excel Source [174]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. 
&gt;
&gt;[DTS.Pipeline] Error: The PrimeOutput method on component &quot;Excel Source&quot; (174) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. 

The value violated the integrity constraints for the column

I have a SQL Server job that runs monthly that runs in server. Job is using an SSIS package and is supposed to extract the data from database and and create an Excel sheet and copy the data into Excel 2003.

I actually got around 140,000 rows from the database due to truncation issue in Excel 2003 (Excel supports 64,000 rows). So I modified the config file to support 2007 Excel format.

    &quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&quot;  +  @[User::FullPath]  + &quot;;Extended Properties=\&quot;Excel 12.0;HDR=YES\&quot;

But when I try to execute the job, it fails showing error message:

&gt; &quot;The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered&quot;

Microsoft.ACE.OLEDB.12.0 is not registered

I am creating a SSIS project via Biml (Using the current version of BimlExpress per the [Varigence BimlExpress page](https://www.varigence.com/BimlExpress)) that uses a script component within a Data Flow Task.  The project is created without issue however errors when it gets to the Script Component:

&gt;Could not load file or assembly &#39;Microsoft.SqlServer.DTSPipelineWrap, Version=14.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91&#39; or one of its dependencies. The system cannot find the file specified.

&gt;at ScriptMain.PreExecute()
   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

I can see this dll within the GAC and when I try to add the reference manually, SSIS states that I can&#39;t add it as it is already included in the project.

However, if I open the Script Component and manually `Build` it - but change nothing else - the Data Flow Task will run through without issue.

To clarify, I know I have changed nothing else as I am recreating this project from the Biml each time I want to test a new approach, which provides a consistent baseline.

---

Does anyone know why a Script Component that includes a reference to a GAC dll, will only work when manually `Built` but not beforehand?

I have used this exact Biml before without issue, though on a different environment.  Worked using Visual Studio 2012 against a SQL Server 2016 instance yet doesn&#39;t here within Visual Studio 2015 (targeting 2012) against a SQL Server 2016 instance.

For further context I have also tested this outputting the SSIS Project targeted at SQL Server 2012, 2014 and 2016 with the exact same error message.

---

#Update

This appears to definitely be a dll version problem in the project as output from BimlExpress, in that if I manually create the `v4.0_14.100.0.0__89845dcd8080cc91` folder with the version 13 dll inside at `C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.DTSPipelineWrap` the script component now errors out with the following:

&gt;System.InvalidCastException: Unable to cast COM object of type &#39;System.__ComObject&#39; to interface type &#39;Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100&#39;. This operation failed because the QueryInterface call on the COM component for the interface with IID &#39;{*ID removed just in case*}&#39; failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

&gt;at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)

&gt;at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

&gt; at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

SSIS Script Component Cannot Load Assembly Until Manually Opened

I&#39;m trying to import a spreadsheet to our database using SSIS.  For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data.  I&#39;ve tried remapping the columns to be nvarchar(255) but it still doesn&#39;t want to select the data it thinks is double, because there are characters in it.  If I try to edit the SSIS package and change the column types in the Excel Source, it won&#39;t let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don&#39;t match.


Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings?  Why does everything from microsoft have to not quite work correctly?

**EDIT:** 

I found this: 

- [PRB: DTS Wizard may not detect Excel column type for mixed data in SQL Server](https://support.microsoft.com/ar-sa/help/236605/prb-dts-wizard-may-not-detect-excel-column-type-for-mixed-data-in-sql)

I sorted my data so that mixed data types would be at the top, and guess what: The problem _reversed_.  Instead of not importing character data, it stopped importing purely numeric data.  Apparently someone doesn&#39;t think 12345 can be represented as a string...

SSIS Excel Import Forcing Incorrect Column Type

I have a server that is capable of creating and running an Excel Import task using the Import Wizard. I am trying to automate that process by using a visual Studio 2010 Integration Services package, that I am developing on that server.

The problem happens when trying to design the package.  I have added an excel connection and pointed it at the Excel file on a local disk (the same file I have already successfully imported using the import wizard). When I add an Excel Source to the DataFlow and specify the excel connection, when I go to the Name Of the Excel Sheet Drop down I just see &quot;No tables or views can be loaded&quot; and get the following error.

&quot;Could not retrieve the table information for the connection manager.
Failed to connect to the source using the connection manager ...&quot;

I can&#39;t find this error logged anywhere and i don&#39;t know why it is failing. The directory is shared to Authenticated users and the file is not in use.

Any ideas how to debug this error? I understand there can be issues running this in 64 bit mode, but does that apply to development?

I should add that it is an excel 2007 file .XLSX and the connection is set to Excel 2007.

2019-11-08 The answer by GavB841 below looks promising, if anyone tries it and it works please let me know. (I am no longer working in this area.)

All Ssis Solutions on Gang of Coders

Total of 22 Ssis Solutions

'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)