locked
Import VFP DBF file to SQL 2005 table through SSIS (Random Error in OLEDB Source) RRS feed

  • Question

  • Scenario:
    I have created a SSIS package where one of the steps is to import .DBF file data  into a SQL 2005 table. This table when loaded it usually has 16 million records. It is the ITEMS table .There is a production m/c where this DBF file is constantly being updated. I also have a sql job which runs daily afternoon,night to copy updated items DBF file from this server to the local server where SSIS is running.This file copy is being done by robocopy command. it copies .DBF,.TBK,.CDX,.FPT,.BAK files . This sql job is always executing correctly. In the DataFlow Task of SSIS for ITEMS table import. I have given the source table as VFP items table under C:\SYS folder. And the destination as items_import table in the SQL 2005 DB.

    Now the problem:
    Sometimes what happens is that the VFp items table does not show up in the source database.But I can see the physical files copied correctly to C:\SYS with robocopy command. The SSIS package would not run since it cannot find the source database. SOmetimes I manually copy all ITEMS file with all extensions (.DBF,.TBK,.CDX,.FPT,.BAK). Sometimes this works.But mostly I'll have to wait till the next file load through robocopy.But this error is purely random.It works for 1 whole week without trouble then stops working all of a sudden. There is a DTS package running using the same data on another m/c .My job is to move away from the DTS and migrate every package on that m/c to this server.This DTS package runs daily as intended without any trouble.

    My assumptions / doubts

    The only explanation I am able to give for this behavior is that the ITEMS.DBF file is huge and may be locking out or something when being copied to different servers.This results in corrupted file or something.DTS package runs because it does not have strict validations as SSIS.What could be causing this?
    How can i fix this?

    Any Help would be greatly appreciated..

    Thanks,
    Remya CV
    Wednesday, August 12, 2009 1:25 PM

Answers

  • Hello Mr. Douglas,

    I added a file system task to copy only the ITEMS.DBF file since that was the one causing problem.
    What I did is i specified the source file (server1:\items.dbf ) and destination file (local\items.dbf)
    Also set overwrite destination property to true.The step executed successfully which means it copied the file successfully.
    But when I go to the next step, dataflow task where this dbf gets imported to sql table it still has the same problem.The items table is still not showing up
    in the oledb source.

    Do you have any other suggestions ? Could it be because of the huge file size? It is 1.1gb in size.
    Please help..

    Thanks in advance,
    Remya CV
    do you have the source component configured to use a dummy .dbf file? this is necessary because the connection is dynamically established at run-time instead of at design-time. however, ssis still needs to know the data source metadata at design-time.  furthermore, DelayValidation = true must be set in both the connection manager and the source component properties for this to work.

    hth

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    • Marked as answer by Tony Tang_YJ Thursday, August 20, 2009 10:09 AM
    Saturday, August 15, 2009 4:54 AM

All replies

  • is there a particular reason why you aren't implementing the ssis file system task to copy the files?

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Thursday, August 13, 2009 7:12 AM
  • Thank You for responding Mr. Douglas.
    Are you suggesting I use File System Task  instead of robocopy command to copy VFP files from one server m/c to other  ?
    I was using it simply because the existing DTS package was loading data using the same. And it never failed.
    I'll try using the file system task to copy and let you know.

    Thanks again,
    Remya CV
    Thursday, August 13, 2009 3:30 PM
  • Thank You for responding Mr. Douglas.
    Are you suggesting I use File System Task  instead of robocopy command to copy VFP files from one server m/c to other  ?
    I was using it simply because the existing DTS package was loading data using the same. And it never failed.
    I'll try using the file system task to copy and let you know.

    Thanks again,
    Remya CV

    yes.  try using the file system task instead.

    hth

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Friday, August 14, 2009 6:31 AM
  • Hello Mr. Douglas,

    I added a file system task to copy only the ITEMS.DBF file since that was the one causing problem.
    What I did is i specified the source file (server1:\items.dbf ) and destination file (local\items.dbf)
    Also set overwrite destination property to true.The step executed successfully which means it copied the file successfully.
    But when I go to the next step, dataflow task where this dbf gets imported to sql table it still has the same problem.The items table is still not showing up
    in the oledb source.

    Do you have any other suggestions ? Could it be because of the huge file size? It is 1.1gb in size.
    Please help..

    Thanks in advance,
    Remya CV
    Friday, August 14, 2009 2:43 PM
  • Hello Mr. Douglas,

    I added a file system task to copy only the ITEMS.DBF file since that was the one causing problem.
    What I did is i specified the source file (server1:\items.dbf ) and destination file (local\items.dbf)
    Also set overwrite destination property to true.The step executed successfully which means it copied the file successfully.
    But when I go to the next step, dataflow task where this dbf gets imported to sql table it still has the same problem.The items table is still not showing up
    in the oledb source.

    Do you have any other suggestions ? Could it be because of the huge file size? It is 1.1gb in size.
    Please help..

    Thanks in advance,
    Remya CV
    do you have the source component configured to use a dummy .dbf file? this is necessary because the connection is dynamically established at run-time instead of at design-time. however, ssis still needs to know the data source metadata at design-time.  furthermore, DelayValidation = true must be set in both the connection manager and the source component properties for this to work.

    hth

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    • Marked as answer by Tony Tang_YJ Thursday, August 20, 2009 10:09 AM
    Saturday, August 15, 2009 4:54 AM
  • Hello Mr. Douglas,

    I have set the DelayValidation property to true . As you said if SSIS needs to
    know the source metadata at design time it will need the source  dbf file. That is what is missing
    The problem of file missing has been encountered at both design time and run time.

    Thanks,
    Remya CV
    Monday, August 17, 2009 11:31 PM
  • Hello Mr. Douglas,

    I have set the DelayValidation property to true . As you said if SSIS needs to
    know the source metadata at design time it will need the source  dbf file. That is what is missing
    The problem of file missing has been encountered at both design time and run time.

    Thanks,
    Remya CV
    if your problem has been solved, then please mark the appropriate post(s) as answered.

    thanks.

    Duane Douglas, MCAD, MCITP | http://www.ssisbi.com | Please mark the post(s) that answered your question.

    SSIS Business Intelligence

    Wednesday, August 19, 2009 5:14 AM