How to create Destination table name with Getdate in SSIS OLEDB destination

Question

How to create Destination table name with Getdate in ssis destination task.

I have one table like Test..but i want to create new table like Test_09Feb2023 in OLEDB destination.

Answer 1

For this to work, you need the following pieces.

Variables

You're going to want to use the SSIS Expression language to build out a date string. You would like to see a format of DDMonYYYY. The SSIS expression language has year/month/day functions that return the numeric equivalent. Your task will be to convert the month number to the abbreviation.

Being lazy, I would tell people that the table name will be Test_YYYY-MM-DD because that's what I have right here

(DT_WSTR, 4)YEAR(@[System::StartTime]) 
+"-" 
+ RIGHT("0" + (DT_WSTR, 2)MONTH(@[System::StartTime]),2) 
+ "-" 
+ RIGHT("0" + (DT_WSTR, 2)DAY(@[System::StartTime]),2)

The important thing to understand is that year/month/day make numbers so you must then cast them as string, thus the (DT_WSTR).

For month or day, those are numbers so it will not return a leading zero. For that, you need to first convert to a string. Then we prepend a zero.

Rather than deal with complex expressions of when to prepend a zero, we always do it and then slice off the last 2 digits. 273/356 times of the year, there's nothing to slice but the logic is easier to maintain if something goes awry.

I would call that Variable DateString of type String.

The next variable I need is the table definition. I click Add, name it TableDefinition of type String. We will again use an Expression.

"CREATE TABLE dbo.[Test_" 
+ @[User::DateString] 
+ "](test_sk int identity(1,1) NOT NULL,  col1 int, col2 int, col3 nvarchar(128), CONSTRAINT [PK__dbo__Test__" 
+ @[User::DateString] 
+"] PRIMARY KEY (test_sk));"

Rendered and formatted

CREATE TABLE dbo.[Test_2023-02-09]
(
    test_sk int IDENTITY(1, 1) NOT NULL
,   col1 int
,   col2 int
,   col3 nvarchar(128)
,   CONSTRAINT [PK__dbo__Test__2023-02-09] PRIMARY KEY(test_sk)
);

As I've gone through the whole answer, I realized I could have used and re-used another variable QualifiedTableName This creates a guaranteed safe name version of our string

"[dbo].[Test_" + @[User::DateString] + "]"

Rendered, that would be

[dbo].[Test_2023-02-09]

Finally, I'd add a third Variable that maybe I only use in development cycles to remove the table. DropTable

"DROP TABLE IF EXISTS dbo.[Test_" + @[User::DateString] + "];"

This uses syntax that is only valid from SQL Server 2017+.

Control flow

Now we can start designing the package itself

The two Execute SQL Tasks will differ only in the value of SourceVariable

Right now, before you do the magic of defining the Data Flow Task, run the SSIS package as-is. This matters because you need to get today's version of the table out there.

Data Flow Magic

Before an SSIS package starts running, it checks that all the resources it needs exist and match the contract it had regarding data types, etc when it was developed. If anything is out of place, the package fails fast so you don't end up with a half-executed pipeline.

We need to tell the Data Flow that it should only perform validation immediately preceding execution. Right click on the Data Flow Task and choose Properties. Change the value of DelayValidation from the default of False to True.

Validation still has to happen, you cannot get an SSIS package to run without eventually performing the check, we have simply delayed it as the preceding task, "SQL Create Table" is responsible for making sure the metadata is valid for the task.

Design the Data Flow task as you need. In the destination, you need to signal that the table name comes from a variable.

If an OLE DB Destination is your sink, then change the "Data access mode"

If your sink is an ADO.Net or ODBC destination, then once they are on the canvas, you will click back to the Control Flow, right click on the Data Flow Task and on the Properties Menu, find Expressions and then the desired Property

ADO.NET Destination demo

ODBC Destination demo

Either way, you'll want to pick our variable @[User::QualifedTableName] As I think about it, one of these, uses double quotes for a qualified table name. Instead of [dbo].[Table_etc] it might expect "dbo"."Table_etc"

Experiment, play. Those are the basic building blocks you'll need to make this happen specific to your environment and problem domain.

But, I need SSIS to inspect the contents of today's file to determine the table structure

If that is your problem definition, then SSIS is not the tool for you. As noted in the previous section, a data flow must pass validation. I cannot have a dynamic "shape" to the data. For that, you need a time machine and use DTS or go cloud and use Azure Data Factory.