Import a set of DBF files in Data Services

[Former Member]

Hello Experts,

I need to import some DBF files in the Data Services.

In each load (monthly), this files change the name and the  quantity.

Any suggestion?

Thanks for listening.

[former_member187605 · Accepted solution]

If they are all the same format, make sure they go into the same folder and use a wildcard (*.dbf) for the input file object in your dataflow.

[Former Member · Accepted solution]

Hello Dirk ,

thanks for listening.

I did this when I create the ODBC connection, but when I try to import the tables , an error occurs .

Is that who you suggested  or exist another procedure to importing the data.

[former_member187605 · Accepted solution]

I was actually assuming you had converted your .dbf files into a format readable by DS first. There are plenty of tools around for converting into csx, xls, xml...

[Former Member · Accepted solution]

Hello Dirk,

Today I have a project in MS-SSIS that import the DBF files directly to the database.

My intention is to do the same in SAP Data Services. Will not stay cool if I tell to the client that I will need first convert the files to import later in the DS.

I will try to import directly to SQL Server through a script within the own Data Services.

If it works, I come back here to disclose the results.

Again, thank for your attention.

[former_member198401 · Accepted solution]

Hi Elton,

I saw your previous posts and found that you are trying to import visual fox Pro DBF file. Please correct me if I am wrong.

I had used the openrowset() function in SQL Server 2008 R2 to import excel data in to SQL Server tables. You can use this function to import the DBF files into SQL server staging tables and then use them in Data services Data store

select * into SomeTable
from  openrowset('VFPOLEDB','C:\SomePath\TESTDATA.DBF';'';
   '','SELECT col1, col2... FROM TESTDATA')

You can then import the target table in to your Data services Data Store.

Not sure how many DBF files you have to import.

Hope this helps!!!

Regards

Arun Sasi

[Former Member · Accepted solution]

Hi Arun Sasi,

As an alternative feature, I thought to do what you suggested.

If all goes well, I come here to share the solution, because I have the need to import files that can vary the name and the quantity, for a ETL executed  monthly.

Thank for your sugestion.

Regards,

Elton de Freitas

[former_member198401 · Accepted solution]

Sure Elton!!

Let me know if there are any issues

Regards

Arun Sasi

[former_member187605 · Accepted solution]

You can actually convert them into a readable format thru a DS script, as well.

[Former Member · Accepted solution]

Hi experts,

Thank you for your suggestions, I managed to make the process using Linked Server in SQL Server.

I also tested it with Openrowset and it worked, but i had to use the 'Microsoft.ACE.OLEDB.12.0' driver because the 'vfpoledb' driver generated an error.

My example with openrowset:

--------------------------------------------------------------------------------------------------

select * from openrowset('Microsoft.ACE.OLEDB.12.0',

'dBASE IV;Database=C:\DBFFILES\','SELECT * FROM process')

--------------------------------------------------------------------------------------------------

*** A small problem we noticed was the file name limit eight characters.

In my example, the name is process.

My example with linked server:

-----------------------------------------------------------------

EXEC master.dbo.sp_addlinkedserver

@server = N'DBFSERVER',

@srvproduct=N'DBF',

@provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'c:\dbffiles',

@provstr=N'dBASE IV'

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'DBFSERVER',

@useself=N'False',

@locallogin=NULL,

@rmtuser=NULL,

@rmtpassword=NULL

-----------------------------------------------------------------

Searching the internet, I saw that many people have this problem with the driver 'vfpoledb'.

Regards,

Elton de Freitas

[former_member198401 · Accepted solution]

Excellent Elton!!

Arun