0

I want to add the date of last day of current month using expression builer in SSIS

Since it is march

I want the file name to be XXXXXX20210331.csv

Sai
  • 47
  • 1
  • 2
  • 7
  • Same problem solving as used in https://stackoverflow.com/questions/66426222/how-to-use-expression-builder-to-create-a-file-name-with-date-even-for-the-first/66426995#66426995 and https://stackoverflow.com/questions/66251558/pass-first-and-last-day-of-the-previous-month-in-ssis-execute-sql-task/66252646#66252646 – billinkc Mar 04 '21 at 15:14
  • Does this answer your question? [Pass first and last day of the previous month in SSIS Execute SQL Task](https://stackoverflow.com/questions/66251558/pass-first-and-last-day-of-the-previous-month-in-ssis-execute-sql-task) – Amira Bedhiafi Mar 04 '21 at 15:17

1 Answers1

1

This approach comes to mind and may be useful with your overall design and avoiding complex expressions:

First generate an Execute SQL Task with a "single row" result set.

Add this to the SQLStatement:

SELECT CONVERT(VARCHAR(8),EOMONTH(GETDATE()),112) as [Date]

Set the Variable Name in the result set to a user variable (that you will need to create "date") and add "Date" to the result name section

When ready to config your Flat File Destination Connection Manager/String:

Using the Expression Builder in the Flat File Connection (go to properties to set a variable --> Connection String for the Flat File Destination.

That said, you will need a working connection string and need to make it dynamic.

Add to the expression:

"filepath" + "XXXXXX" + (DT_STR, 8, 1252) @[User::date] + ".csv"

Hit "Evaluate value" to double check the full file path to be used in your destination with dynamic file name

Jatin Morar
  • 164
  • 1
  • 7