asked
Access and SQL sync
I have a table in Access and a table in SQL. I need to insert records from access into sql that are not already in that table and also sync sql to access so that at all times those two tables are matching. the Job will be run once an hour. Any ideas? I can use SSIS but it is the merging i'm lost on.
Well i've created few procedures and some holding tables. I'm at the final peice of the ssis package. Where i bring the additional items needed in the access table and i cannot figure this one out. I have tried so much. I am going from sql table. Converting using data conversion and then into access table. I can not figure the right combination to get access to take the sql information? Now any ideas? I've gotten error unicode to non unicode once i make that go away i get error external column needs to be updated.
NO one has any idea? Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning. Error: 0xC0202009 at Data Flow Task, OLE DB Destination [1076]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E03. Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (1076) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (1089). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post
Execute phase is beginning. Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (1076)" wrote 0 rows. Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task SSIS package "xxxxxxxxx" finished: Success. I have gotten one field i'm trying to insert so that i can try to fix then expand to rest of the columns. I can't even get one column to insert into access.
I created the second part to my ssis package that does the exact same thing but to a different table and it works. So does anyone have a clue how i can find out what is the problem with the one table in ACCESS?
Question: Are you using a SQL backend for your access database or access tables? If using access tables I would firstly ask why when you have SQL? If you are using SQL for both then: I would try using ssis and using the 'execute sql command' object, then get it to run a stroed procedure in your sql database.
16 People are following this question.
