question

artistlover avatar image
artistlover 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.
ssisaccessmergesynchronisation
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
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
0 Likes 0 ·
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.
0 Likes 0 ·
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?
0 Likes 0 ·

1 Answer

· Write an Answer
SimonThompson avatar image
SimonThompson answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.