0

So I'm using SSIS (SQL Server 2012) to upload/load some data from a dbf into a table. Basically, I'm using a For-Loop Container to read the dbfs (note: hundreds of dbfs) and put the data in the table. For the most part so far so good. However, there are a few dbfs (like 5 or 10 and automatically created by another process I have no control over) that have a slightly different schema (ie. missing a column X and has a new column Y). So I'm wondering if there is a way to make those columns "optional" or just automatically make them return NULL. Or some way to have SSIS check the two schemas and have "do the right thing".

Currently, when SSIS runs into these dbfs, it gives me The external columns for OLE DB Destination are out of synchronization with the data source columns. error.

dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
  • 1
    I suggest you create a data flow for each type of schema ad if the first data flow fails, try the next one. You may also wish to inspect the schema beforehand (by opening the DBF directly) and work out beforehand which data flow to run rather than waiting for it to error. – Nick.Mc Feb 12 '14 at 01:47
  • How should I set up the data flows exactly? – dotnetN00b Feb 12 '14 at 03:40
  • Find examples of DBF files with the 'normal' schema and the 'abnormal' schema. Build a seperate data flow for the abnormal schema. Then you need to open up both DBF's in notepad and see if there is anything that easily identifies the difference. Now build some code to recognise that in a script task and use that script task to decide which data flow is executed. Can you verify, there are basically two different schemas? or are there many different schemas? This may be impractical if there are many different schemas. – Nick.Mc Feb 12 '14 at 04:32
  • As far as I know there are only two at the moment. It would be nice if there was a way to just look at the columns I'm interested in and pull the data from that. – dotnetN00b Feb 12 '14 at 13:28
  • You could try using a SELECT statement as your source. That would limit the columns coming out of the data source. But your driver needs to support using a SELECT. What driver are you using by the way? You might be able to use some script or BIML to build the package dynamically but for just two different schemas I think this is way over complicating it. SSIS just likes metadata to be consistent because it makes other pieces of the process so much easier. – Nick.Mc Feb 12 '14 at 23:17
  • Visual FoxPro drivers – dotnetN00b Feb 13 '14 at 18:50
  • You should ascertain how many different schemas there are to deal with before you come up with a solution. You should split it into two steps. One step gets the DBF's into a consistent table regardless of schema and the other step performs transformation on the consistent table. – Nick.Mc Feb 14 '14 at 00:49
  • Just came accross this: http://stackoverflow.com/questions/4281237/ssis-flat-files-with-variable-column-numbers which suggests you create a parent package which evaluates the file type then programatically alters the child package then calls it.... IMHO it would still just be easier to have two custom packages and selectively call them. – Nick.Mc Feb 18 '14 at 23:06

0 Answers0