CODEWITHSUNDEEP
sql-serverssis
RandyMcKay
RandyMcKay

Reputation: 326

Need advice on SSIS solution with variable based connection managers

I am working on the SSIS solution to get 20 different txt files from a specific folder and upload them to different SQL Server database tables. I added a mapping table with table_name, file_name, file_path, full_connection_string. How do I tell connection manager which connection to use for a certain file? Which variables/parameters to use and where?

I do not want to have 20 txt connections(for the known filename difference) and 20 database connections. All online tutorials are old and don't match Visual Studio 2019 UI.

Any help is highly appreciated!

Upvotes: 1

Views: 193

Answers (2)

FLICKER
FLICKER

Reputation: 6693

You can have parametric connection string.

  1. Define the variable: _Server (string)
  2. Select the Connection
  3. In Properties window, select [Expression]
  4. Use your variable for (SereverName)

Obviously you have a loop and you can load the proper server name into your variable so in each iteration, the connection will be connected to specific server.

enter image description here

Upvotes: 2

billinkc
billinkc

Reputation: 61259

You will need one database connection manager for each target database as this is scoped at the database level.

You will need a flat file connection manager for each unique file metadata. You can have twenty Sales-date.txt files that use a single flat file connection manager and then expressions will take care of consuming the different files.

However, if you have Sales.txt and Customers.txt, the metadata, aka the columns inside the file, are going to be different and that's fine but you'll have to create a flat file connection manager for each of those types. This is the contract you're making with the SSIS engine - I promise all of the files this FFCM will touch conform to this standard. You will also need to have a Data Flow Task for each of these FFCMs as the engine computes how many rows of data it can operate on at a time based on the type and column constraints in the source.

If it were me, I'd spend a few days looking at Biml. Biml is the Business Intelligence Markup Language and what it allows you to do is describe your problem in a repeatable way. i.e. For each of these file types, I need an SSIS package that is a for each file enumerator to pick up the current file. Inside that, a data flow task to ingest the file. Then a File System Task to archive the file out of the working folder.

You've already started down this path identifying your metadata ( table_name, file_name, file_path, full_connection_string) the only thing remaining is to describe the contents of your files. If you look through my SO answers, you'll find plenty of answers that use Biml to create a reproducible solution.

Upvotes: 1

Related Questions