SSIS Development- how to handle with new Table creation at source every 15 days

Question

I am developing a SSIS Package, which consume data from Source SQL and target FLATFILE.

The one challenges I am facing, please help me how to solve it.

The New table got created in SQL Source by Upstream every 2 week, How to handle such scenario. I don't want to modify SSIS Package every time for new table which got created. The Schema of new table are not same.

Is There any way to handle this by config file or any control file. Or to achieve this, do I need to shift to different technology stack. Please help

Many Thank

Answer 1

"The Schema of new table are not same"

Then an SSIS Data Flow is not the right technology for you. A Data Flow is tightly bound to the source and destination metadata and changing schema ain't gonna work¹.

"do I need to shift to different technology stack"

Maybe? Maybe not. SSIS can still offer benefits as an execution framework with the native logging, configuration, and access to a host of native components plus the .NET runtime for anything else you might need to do.

How would I solve this?

Given that you need to export the contents of an arbitrary table to a flat file, I would use my trust friend BCP

As a sample invocation, the following would export a table named MyTable to a tab delimited file using integrated security

bcp dbo.MyTable out "C:\temp\AdventureWorks.dbo.MyTable.txt" -S ERECH\DEV2019UTF8 -d AdventureWorks -T -c -a 32768

You can invoke bcp from SSIS via an Execute Process Task.

How do you handle the changing table name? Probably a query like

-- Generate a list of tables created in the past 16 days
-- Make the table name safe for consumption by BCP via quotename
SELECT
    CONCAT(QUOTENAME(S.name), '.', QUOTENAME(T.name)) AS QualifiedTable, T.create_date 
FROM 
    sys.schemas AS S 
    INNER JOIN 
        sys.tables AS T 
        ON T.schema_id = S.schema_id 
WHERE 
    T.create_date > DATEADD(DAY, -16, GETDATE()) 
ORDER BY T.create_date DESC;

¹ You could conceivably make it work assuming there are always common core elements and you only need to export those entities.

For example, given these table definitions, you could extract the first 3 columns, with some type casting in the source queries and always fill an output file

CREATE TABLE dbo.Week0
(
    id varchar(10)
,   Col1 float
,   Col2 int
,   ExtraColumn varchar(50)
);

CREATE TABLE dbo.Week1
(
    externalId int
,   Col11 float
,   Col2 varchar(50)
);

It would be a lot of dynamic query generation and hope and guesswork but it can be done, but probably shouldn't be.