Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Having Issues Importing .dbf Files with SSIS


Having Issues Importing .dbf Files with SSIS

Author
Message
Tslink
Tslink
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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. Sad
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8308 Visits: 19449
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Tslink
Tslink
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Tslink
Tslink
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
mtalasaz
mtalasaz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
Hi,
May I have your solution in detail.
I have the same problem and I can not find anything.
Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search