3

I am following a Udemy Course Learn ETL using SSIS. The first simple task is to transfer data from an excel file to a database.

The only change I have made is I am trying to transfer to a PostgreSQL server instead of a Microsoft SQL Server. I therefore had to install SSDT for Visual Studio first, and get the ODBC driver necessary to create a ODBC Destination for the package.

All well so far, but then when I try and run the package I just get:

SSIS package "Visual Studio 2017\Projects\Excel_SQL\Excel_SQL\Package.dtsx" starting. Information: 0x4004300A at Excel to SQL, SSIS.Pipeline: Validation phase is beginning. Information: 0x4004300A at Excel to SQL, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Excel to SQL, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Excel to SQL, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Excel to SQL, SSIS.Pipeline: Execute phase is beginning. SSIS package "Visual Studio 2017\Projects\Excel_SQL\Excel_SQL\Package.dtsx" finished: Canceled. The program '[14368] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

No data transfers over. The excel file is very simple, excel 97-2003 as the connection expects, contains 2 columns only, rollnumber and marks, rollnumber has 11 rows of data going 1 to 11, and then some random marks in the marks column.

My database on Postgres is set up with these 2 columns, as numeric types.

I really cannot figure out what is going wrong.

I have seen some similar questions on stack overflow, but that was around the file type not being correct:

I don't think that's my issue.

Can anyone please advise?

Thank you.

Hadi
  • 36,233
  • 13
  • 65
  • 124
mampoa
  • 51
  • 1
  • 5

2 Answers2

2

I think the situation is not clear, but there are many suggestions that you can follow:

(1) Try to run the package in 32-bit

The issue may be caused if the package is trying to run in 64-bit mode and you don't have installed the relevant references, try to execute the package in 32-bit mode:

Package Properties >> Debugging >> Run64BitRuntime = false

(2) AccessDatabaseEngine is not installed

The issue may be caused if the Office connectivity components for microsoft Excel are missing, check that you have installed them:

(3) Make sure you have followed the appropriate steps to create ODBC Destination

You can follow this article in order to create a package that import data to postgres, check that all steps are done correctly:

(4) Doing some workarounds

In order to specify the error source, try to replace the Postgres destination with a Flat File Destination, if the package is executed successfully then the problem is with the ODBC Destination, also try to replace the excel source with FLat File Source, if the package is executed successfully then the problem is with the Excel Source.

If you are new to SSIS, som articles can help:

(5) Try to Use SQL Import Export Wizard

If you have SQL Server Installed, try to use the Excel Import Export wizard to create and execute the package:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Hi, Thanks for the quick response. 1) I did indeed install the 32 bit ODBC driver to get things to work up to this stage, as up until then I could not connect to the database. I also tested the connection and it worked. So I can connect to the database at least. I will try going through your other linked helpful items. Thank you. – mampoa Feb 02 '19 at 12:05
0

So, in this case it was that I just had to move the (Source) excel file out to my E:\ drive. Perhaps the path it was in was too long? Only 3 folders in from E:\ but...it worked.

Can anyone explain why that was the issue? Nothing pointed to that from the error messages.

mampoa
  • 51
  • 1
  • 5