0

I am very new to SSIS. I have a task of taking data from ODBC source A, then transport that data to access, thereafter to SQL. I believe SSIS can do this task. However, according to various examples I have seen, they use a single source and destination. I couldn't find any method for using 3 or more connectors at a time. TO summarize again, I have to migrate data in following manner: ODBC source A -> Access -> SQL Server (when import is over at access)

How can I achieve this?

Please don't ask me to remove access or any other data source as proper mappings and data in SQL server is moved if the ODBC data goes into Access first.

Sahil Tiwari
  • 159
  • 2
  • 12
  • when you say "when import is over at access" what does that mean? Does the data move 2 times. Once from ODBC Source A -> Access. Then it's moved from Access -> SQL server? – Tim Mylott Oct 15 '18 at 16:22

2 Answers2

1

From what I can gather you only want the data copied to its final SQL Server destination after it has been copied to Access.

  1. Copy data from ODBC Source to Access
  2. Then copy data from Access to SQL Server

You can use 2 data flows to accomplish that is SSIS:

enter image description here

Tim Mylott
  • 2,553
  • 1
  • 5
  • 11
0

After your data source, use a Multicast, which allows the data to flow to multiple destinations. It will look something like this:

enter image description here

(Ignore the errors on the destinations, as I'm including this as a mock-up.)

Community
  • 1
  • 1
Jeremy J.
  • 697
  • 4
  • 9
  • My main destination is SQL server which will only get data once total data comes into Access from ODBC source. – Sahil Tiwari Oct 15 '18 at 16:20
  • There seems to be something I am not understanding. If that's what you want to do, then just have two Data Flow Tasks. The first one moves the data from ODBC source A to Access. The second one moves it from Access to SQL Server. Are you changing the data in Access after you load it and before you move it to SQL Server? If not, then I don't understand why you are doing it like this. – Jeremy J. Oct 15 '18 at 16:39
  • Actually the ODBC is a CRM data source which doesn't give proper schemas when linked directly with SQL server. So I am using Access as an intermediate repository as I am able to view schemas and columns of all those CRM tables. – Sahil Tiwari Oct 15 '18 at 19:41