CODEWITHSUNDEEP
sql-serverazuressisdata-warehouseazure-synapse
Brian Wheat
Brian Wheat

Reputation: 160

Extract SQL Azure Federated Database to Data Warehouse with SSIS

I am trying to transfer our production data to a data warehouse for reporting purposes. I've tried following the "Importing to Federations" section from the SSIS for Azure and Hybrid Data Movement, but I need to move data from my federations to the data warehouse. I've also found a good resource at SQL Server Central, but I still can't seem to bring up the federated tables in the data flow wizards. Nor can I add a Use FedDB statement in a SQL command in the ODBC (connection type needed for a SQL Azure DB) source wizard.

Upvotes: 2

Views: 377

Answers (1)

billinkc
billinkc

Reputation: 61201

I built out a prototype package, based on my assumption of a Vertical sharding (same schema spread across multiple instances)

What you'll want to do is create an ADO.NET Connection Manager and as the Provider, select ".Net Providers\Odbc Data Provider."

The connection string will look something like the below. As the first link you provided indicates, be certain that you have authorized the IP and that you specify the Database

Driver={SQL Server Native Client 11.0};Server=tcp: abcdefghi.database.windows.net;Database=romulus;

Control flow

Control Flow

I have a Foreach Loop Container set up so that I can enumerate through all the instances in my federation. Each pass through the loop generates the connection string to the current instance. I assign that into a Variable, SourceConnectionString of type String.

I then have an Expression set on ADO.NET Connection Manager to set the ConnectionString property to @[User::SourceConnectionString]. This will ensure that our connection actually changes during enumeration.

Data Flow

A Data Flows derives its performance by keeping strict tabs on the metadata surrounding the source and destination. You will want to create a data flow per table you need to contend with. There are strategies for running multiple data flows in parallel which I am not addressing here. I'm sure Andy Leonard covers it in his Stairway to Integration Services series that you've already found.

I've structured mine much as you see in the linked SSC article

Data Flow

You have for source components basically either OLE DB or an ADO.NET component. Since we're working with Azure, we'll need the "ADO NET Source" component.

Lookup Components can use an OLE DB Connection Manager or a Cache Connection Manager. Since you are pushing to an on premises (misspelled in my screenshot) instance, you can use an OLE DB Connection Manager to handle your lookups.

Really, except for the source and the enumeration through the federation, there's very little difference between this answer and what's in the article.

Upvotes: 2

Related Questions