Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Having Issues Importing .dbf Files with SSIS Expand / Collapse
Author
Message
Posted Tuesday, November 3, 2009 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 1:16 PM
Points: 4, Visits: 22
Hi,

I've recently started using Sql Server SSIS packages and I'm having an issues importing more then one dbf file from a network drive into a sql table. I've followed all the directions for both of the links below yet I'm unable to use a variable name with a blank variable name as they state... to make it select all files. Here is a list what I need this package to do.


http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
http://www.infrandom.com/42/Default.aspx?tabid=53&EntryID=2


1.) Truncate the staging table in SQl Server 2008 - No issues
2.) foreach loop to pull data from 1+ dbf files(file names will never be the same...yet will always .dbf) and the data will be inserted into a staging table in sql sever 2008. Works fine with one .dbf file hard coded yet won't let me do multiple files.

3.) Once all .dbf files have been inserted into the table move all files (.dbf and .dbt) into a folder to be renamed and archived. Archived name will change the file to yyyymmdd.


Can someone help me do this dynamically....I've wasted a day trying trying to do this.
Post #813082
Posted Tuesday, November 3, 2009 10:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:51 PM
Points: 5,236, Visits: 12,149
What is the format of the DBF files? Are these database files? What driver are you using to connect to them?

Is the meta data in all the source files the same? (Field names, types, number of fields.)



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #813086
Posted Tuesday, November 3, 2009 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 1:16 PM
Points: 4, Visits: 22
I don't much information about the files themselves due to their coming from an external company to ours. Yet what I do know is I'm recieveing two files in a zip. One file is .dbt and the other is a .dbf file. In order to view the data I need both files in the same folder so I would assume it is a database file.

The connection I'm currently using is to get to the files is the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. Once in the connection manager i'm entering the location C:\Temp\test and going in the advanced section and changing the extened properties to DBASE IV.

If I select the dbf file in preview under the dataflow/datasource the data is displayed.

Fields are as follows and yes it is the same in both dbf files.

Element #01 : CLMNT_SSN - Claimant SSN
Element #02 : CLMNT_NAME - Claimant Name
Element #03 : CLAIM_NUM - Claim Number
Element #04 : CLM_STATUS - Claim Status
Element #05 : ACC_DATE - Date-Accident
Element #06 : RPTD_DATE - Date-Reported
Element #07 : TOT_LWD - Tot Claim Lost Work Days
Element #08 : RTWFULLDUT - U-RTW-Full-Duty-Date
Element #09 : LOSS_DESC - Description-Claim




Post #813096
Posted Tuesday, November 3, 2009 1:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 1:16 PM
Points: 4, Visits: 22
Well I solved my own issue. I used two variables and a script. In the script it pulled the current file name from the for each loop and had it pass it into the variable that was used to design the table variable. So every time the for each loop runs the script runs first and overwrites the table variable with the new file name. Then the new file is used.

Post #813187
Posted Friday, October 18, 2013 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 9:59 AM
Points: 1, Visits: 0
Hi,
May I have your solution in detail.
I have the same problem and I can not find anything.
Thanks
Post #1506250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse