SSIS Question - Syntax question - Help me with building the expression

Question

SSIS Question - Syntax question - Help me with building the expression

mw_sql_developer

SSCoach

Points: 19445
More actions
May 30, 2017 at 2:51 pm

#330748

See the pic below: As it is it works fine. It replaces the YYYYMMDD part in the file name with the current date So YYYYMMDD becomes 20170530
Instead of the current date I want the YYYYMMDD part to get replaced by the value in the user variable @ReportDt. Can you help ?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

First, posting the screenshot is nice, but posting that text is also helpful. For others who want to see and play with the text:
@[User::File_Path] + (REPLACE(@[User::File_Name],"YYYYMMDD",REPLACE((DT_WSTR, 10) (DT_DBDATE)GETDATE(),"-","")))

I believe. (my eyeballs may have read a comma incorrectly, but I think that is correct).
If I understand what this is doing (which I am a little rusty on), replacing YYYYMMDD in @[User::File_Name] with the result og GETDATE() but having the -'s stripped out and thus giving the GETDATE() in YYYYMMDD format.
SO I presume that File_Name is something like "BACKUPYYYYMMDD.bak" and File_Path would be something like "C:\BACKUPS\"

I think this is what you want if I am understanding your request properly, but I also think you may want to learn more about how those expressions work:
@[User::File_Path] + (REPLACE(@[User::File_Name],"YYYYMMDD",@[User::ReportDt]))

That should replace YYYYMMDD with the value in @ReportDt which I think is what you were asking for.
If that gives you the correct result, do you understand WHY it gives you the correct result?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

bmg002 - Tuesday, May 30, 2017 4:01 PM
First, posting the screenshot is nice, but posting that text is also helpful. For others who want to see and play with the text:
@[User::File_Path] + (REPLACE(@[User::File_Name],"YYYYMMDD",REPLACE((DT_WSTR, 10) (DT_DBDATE)GETDATE(),"-","")))
I believe. (my eyeballs may have read a comma incorrectly, but I think that is correct).
If I understand what this is doing (which I am a little rusty on), replacing YYYYMMDD in @[User::File_Name] with the result og GETDATE() but having the -'s stripped out and thus giving the GETDATE() in YYYYMMDD format.
SO I presume that File_Name is something like "BACKUPYYYYMMDD.bak" and File_Path would be something like "C:\BACKUPS\"
I think this is what you want if I am understanding your request properly, but I also think you may want to learn more about how those expressions work:
@[User::File_Path] + (REPLACE(@[User::File_Name],"YYYYMMDD",@[User::ReportDt]))
That should replace YYYYMMDD with the value in @ReportDt which I think is what you were asking for.
If that gives you the correct result, do you understand WHY it gives you the correct result?

I shouldn't have posted this. .. After posting i realized this was just a matter if using the replace function. Cool!

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Heh. Glad you figured it out.

Was your solution similar (or identical) to mine?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

bmg002 - Wednesday, May 31, 2017 11:16 AM
Heh. Glad you figured it out.
Was your solution similar (or identical) to mine?

Identical