Importing multiple dbase files in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Importing multiple dbase files in SSIS

Author

Topic

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-22 : 13:15:17

Hi, I am new to SSIS

I am having issue in importing multiple dbase files.
All dbase files are in this folder C:\DailydbaseBackup\Gold
I am using for each loop container. I am giving this as a value for FileName variable.

If i give the value as c:\DailydbaseBackup\GolGlms011.dbf. Then
My package ran successfuly. However, it looped and loaded the 1st file 3 times instead of loading 3 files in the directory.

I need to complete this task ASAP. Please help me to solve this.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 13:17:28

see this to understand how to iterate through files in a folder

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-23 : 01:45:55

I have tried using user::Filename variable. I am getting the following error:

[OLE DB Source 1 [319]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\DailydbaseBackup\Gold" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "C:\DailydbaseBackup\Gold"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'C:\DailydbaseBackup\Gold\Glms011.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

If i give the first file name as value of the FileName variable. Then it ran successfully, but it inserts first file 3 times instead of 3 files. Please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 01:59:06

what have you selected in for each loop? filename alone or fully qualified?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-23 : 02:24:26

Fully Qualified. I am getting the following errors.

[OLE DB Source 1 [319]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\DailydbaseBackup\Gold" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "C:\DailydbaseBackup\Gold"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'C:\DailydbaseBackup\Gold\Glms011.DBF' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

C:\DailydbaseBackup\Gold is having 3 dbase files. I am running the package manually.

Can you please help me to resolve this?

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 02:55:23

i feel like its problem with way path value is passing. are you passing path from a variable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-23 : 03:03:42

Thanks for your response. Here am explaining what i have done.
I created a variable FileName. I given the following in expression builder for connection string.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"dbase 5.0;HDR=YES\";"

In the connection manager properties, server name showing as C:\DailydbaseBackup\Gold

This path is containing all the dbase files.

I totally confused why am getting the above errors.

it would be great, if you could help to resolve this.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 05:09:46

do you need to send filename also for dbf connection? i think you need to send only the path not filename. filename you need to select as source table inside your OLEDB source

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-23 : 05:25:11

I am giving only path name as value of a variable.

I am totally frustrated to resolve these errors.

Can you please help to resolve this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 05:30:51

and i'm assuming ssis is running in server itself and your path is also on server. Else you might have to use unc path

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-23 : 05:40:12

Thanks. But, it is working fine, when i import single dbase file from the same folder.

I am getting the following error:

TITLE: Microsoft Visual Studio
------------------------------

Error at Package [Connection manager "C:\DailydbaseBackup\Gold"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".

Error at Data Flow Task [OLE DB Source 1 [319]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "C:\DailydbaseBackup\Gold" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

please help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 07:14:39

Answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164574

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AnuVijay
Starting Member

32 Posts

Posted - 2011-08-24 : 03:42:09

if we import single dbase file using the same connection string. it is working fine.
I am getting the errors when i add for each loop container to it.

please help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 08:00:17

the only thing that could go wrong as per your explanation is dynamically getting filename for loop thats why i asked you to print out and check the value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources