November 3, 2009 at 10:21 am
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. 🙁
November 3, 2009 at 10:26 am
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.)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2009 at 10:39 am
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
November 3, 2009 at 1:25 pm
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.
October 18, 2013 at 10:00 am
Hi,
May I have your solution in detail.
I have the same problem and I can not find anything.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply