2

I am trying to create some sort of automation whereby I can generate a series of pipe-delimited text extracts for about 100 different tables each month. Each extract would be based on a simple query like this:

SELECT *
FROM tablename
WHERE AsOfDate = 'currentmonth'

where both tablename and currentmonth would be variables. The tablename variable name would change for each of the tables but currentmonth would remain the same throughout the execution.

I have been attempting to build an SSIS package that uses a ForEach Loop container that runs through a list of all the table names and passes that variable into a SQL string, which is then used by the OLE DB Data source in the data flow.

However, all of these tables have different columns. Based on what I can tell, it would not be feasible to do a simple OLE DB Source to a Flat File Destination within that loop container since the Flat File Connection Manager must be configured to account for the different columns of each table.

Would there be any feasible way to do this outside of configuring the process manually for each of the 100+ tables?

RMQ1447
  • 21
  • 1

1 Answers1

-1

You could look into BiML which programmatically creates your dataflows based on metadata.

Or you could use a Script task that loops through the tables, loops through their columns, and generates text files instead of using any dataflow at all.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52