Search code examples
sql-serverodbcfoxprossis

SSIS: Execute package in 32 bit mode, & copy from one source to another

I need to (on a regular basis) move data stored in a FoxPro db to SQL. I've been trying to get this working to no avail..

I ended up finding this link which seems to be yielding better results...

Also I'm a complete SSIS n00b, I've literally only started up the IDE for the first time today.

So far I've done the following... (excuse my use of images if its not necessary)

(I've also set Run64BitRuntime to false, I read somewhere that the FoxPro ODBC drivers are 32 bit, so don't know if it help or not/since i get the same error with this setting on or off)

1.) Created the control flow & added a data flow task to it Control Flow

2.)Created the connection to SQL Destination and my FoxPro Source and linked them up

Dataflow

3.) On execution I get an error enter image description here

I also saw this on the execution results page (Didn't realize once its attached it'll be this minuscule...) But its basically

[OLE DB Source 1] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "\foo\c$\bar" failed with error code 0xC0209303.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.

enter image description here

4.) I then had a look at my FoxPro connection, and as far as I know it seems fine. (Just as an FYI. I'm no sure how the FoxPro db works, (I'm also completely new to it), but it seems like a bunch of loose files, eg. 1 file per table in the db, so I'm not sure if I can connect to a single file/should it be a directory or what) Connection

Any ideas... :\

UPDATE: There is more to step 3 than I noticed.

[Connection manager "\foo\c$\bar"] Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider VFPOLEDB.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

how would i get the package to execute in 32 bit mode?

Solution

  • I repeated my exact steps as in my question. After looking at my original post (point no 3 to be exact) you'll see a section:

    does not have a value for the ID property

    From the image below:

    does not have a value for the ID property

    This was a bit of a learn to read error. I'm never mapped the two sources/tables (Caused by my SSIS inexperience).

    With Regards to Copying the Data:

    For anyone that comes across this post, THIS is the exact route I took to copy my data to my SQL DB (Solution 1)

    • Create a new SQL Server Integration Services(SSIS) package.
    • Add a Data> Flow Task(DFT) to the package.
    • Select the DFT, and go to Data Flow designer page.
    • Add a OLE DB Source to the package.
    • Double-click the OLE DB Source, click "New" > "New" to create a connection to Visual FoxPro.
    • In the Connection Manager, please select "Microsoft OLE DB Provider for Visual FoxPro" and type the file path of a Visual FoxPro database(e.g. C:\Address.dbc).
    • Test the connection by clicking button "Test Connection" Click "OK" > "OK" to apply.
    • Now, we can select a table from the Visual FoxPro database in the OLE DB Source editor.