Adding the Current Date to a SSIS Column

Question

Post reply

Adding the Current Date to a SSIS Column

ganteng1

Ten Centuries

Points: 1183
More actions
May 11, 2014 at 9:49 pm

#288755

Hi Everyone.
Can someone please tell me how to add the current date to a SSIS column?
Thanks

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ganteng1 Ten Centuries Points: 1183 More actions · Answer 1

Hi Everyone.

Sorry, my question above is not complete. It should be as follows:

How do I add the current date to a SSIS column field header?

Thanks

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

When you say column header you mean its name, like to make ColumnName_20140512 something like that ?

ganteng1 Ten Centuries Points: 1183 More actions · Answer 3

ganteng1

Ten Centuries

Points: 1183

May 11, 2014 at 11:46 pm

#1712532

Hi there.

Yes the column headers name.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Share your scenario why you actually need this working, and at which point you need this column? need more understanding.

ganteng1 Ten Centuries Points: 1183 More actions · Answer 5

Hi. I'm not really sure how to better demonstrate this. Basically, I need my field/column names to be dynamic.

Thanks

rhythmk SSCertifiable Points: 7271 More actions · Answer 6

Not sure about your requirement.However you can always define a variable to store the curent date value and can use it as per your requirement whether in Execute SQL Task or Script Component \ Task.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

What is the source? What is the destination?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 8

ganteng1 (5/12/2014)
Hi. I'm not really sure how to better demonstrate this. Basically, I need my field/column names to be dynamic.
Thanks

I still cannot understand the context of your situation but the thing is if you want to add a column at runtime dataflow will crash like it has never crashed before, as SSIS has strongly-typed metadata which means you cannot change it at runtime.

So either you will need to create the newly package on or update the package at the design time.

if you want to change it on run time then you will need to do this

Developing a Custom Data Flow Component

OR

you can also see this Stairway to Biml [/url]

hope it helps

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 9

It is simple enough to have a column in a SQL statement that represents the current date. You can even simplify the SQL statement to just return the current date with: Select GetDate(). By using getDate(), you can define a data flow column that is always going to be a datetime type and won't blow up when the package is ultimately deployed and run.

Nevyn SSChampion Points: 14210 More actions · Answer 10

Could you explain why you would want to change the name of an SSIS column?

I can get (sort of) wanting dynamic output to a destination, but that could be done without also renaming the column in SSIS itself.

Maybe you should take a step back and give us a big picture explanation of what you are trying to accomplish overall. What does your source data look like, and how does it need to be transformed / where does it have to go?

ganteng1 Ten Centuries Points: 1183 More actions · Answer 11

Thanks for the input everyone. Really appreciate it.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

ganteng1 (5/12/2014)
Hi. I'm not really sure how to better demonstrate this. Basically, I need my field/column names to be dynamic.
Thanks

I know a lot of people will scoff at it but an EAV (Entity, Attribute, Value) or NVP (Name, Value Pair) table to store the data might be just what the doctor ordered here. From there, it's simple enough to do a dynamic CROSS TAB to erg out the info you want in the horizontal format you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 13

Jeff, BTW, I really enjoy your avatar. I could probably spend the whole day admiring it. 🙂

herladygeekedness SSCertifiable Points: 5878 More actions · Answer 14

I am puzzled why a date field would require itself to be named after the current date that is presumably the exact content it holds, but, not the wackiest business rule I've ever heard... we add a generic LastRefreshDate field that gets popped with GetDate().

Are you expecting to end up with a column added every time the pkg runs with That Date as fieldname or will there ever only be One Date field? So it runs every day for a week, does it have 7 date columns or just one?

You could add an Execute SQL step to rename one generic date field to = Today but your pkg will error next time because metadata changed.

I would urge you to push back on this requirement as I am hoping they misspoke! it's difficult to understand the need for a field named = to its value and have field name change daily. Anything, anyone consuming that table will also have to change their reports, etc., every day. maybe this is necessary in your environment, in which case I would be interested in knowing what line of business your company is in.