0

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.

2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Feb 9, 2023 at 7:20
  • I want create the new table dynamically with date(tablename_09022023 like.. using source table name in destination server Feb 9, 2023 at 7:41

1 Answer 1

0

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

enter image description here

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

enter image description here

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"

enter image description here

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

enter image description here

ODBC Destination demo

enter image description here

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.

5
  • I have create the table with date using the variables, but here showing the error Oledb destination not existing the table.how i fix this issue. Feb 10, 2023 at 11:56
  • I'm sorry, I don't understand your comment. Click Edit on your original question and paste in a screenshot of 1) the OLE DB Destination, 2) the Variables used 3) the running package with the errors fully visible
    – billinkc
    Feb 10, 2023 at 15:53
  • Also, did you follow the instructions? Specifically 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.
    – billinkc
    Feb 10, 2023 at 15:54
  • Hresult: 0x80004005 Description: "The metadata could not be determined because every code path results in an error; see previous errors for some of these.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid object name 'dbo.Items_2023-02-09'.". Error at Data Flow Task [OLE DB Destination [142]]: Failed to open a fastload rowset for "[dbo].[Items_2023-02-09]". Check that the object exists in the database. Feb 11, 2023 at 3:53
  • After create table dynamically using variables, but im not forward to select destination table or source nd destination mappings is not possible.. Feb 11, 2023 at 4:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.