Why CURRENT_DATE in SSIS works but not in SQL?

I have a fully functional legacy SSIS system which I have inherited. I am using Visual Studio 2015 to view the packages. I also have a connection to Microsoft SQL using Microsoft SQL Server Management Studio 2014.

Note. I am trying to be extremely accurate here and It's entirely possible that I am misrepresenting this somehow. All I know is the version of the software I use to access these systems, I do not know if those are the same versions they are run in (if that makes sense)?

In Visual Studio. I see steps such as an "Execute SQL Task Editor" (this is what the popup window shows when I go to edit it) and in this step I see a query which contains the function name 'CURRENT_DATE'. Everything appears to be working correctly, however, If I take that same exact SQL command out of the SSIS package and run it directly on the database it does not work saying that CURRENT_DATE is not a command.

Why?

I think i need to be more clear here. The question is not why does the command not work. I already know that. The question is why DOES it work in SSIS? I need to know what commands are working in SSIS and why because we are going to migrate to a cloud based version of it (or something) and I am trying to determine what will break.

screenshots

As others have deduced, while the SSIS package may run on the SQL Server, it works because the package also connects to a different location and database. It's using an ODBC connection to talk to a different kind of database entirely, with different functions for handling dates... a common situation among SQL dialects.

Doing some digging for a platform supporting ALL of DAYNAME, TO_CHAR() (both with and without a format argument), and CURRENT_DATE, and I strongly suspect this is talking to a Snowflake database.

If it helps, the direct SQL Server translation of that code looks like this:

SELECT
    FORMAT(getdate(), 'yyyy/MM/dd/') FileDate,
    CASE WHEN datepart(DW, getdate()) = 0 
         THEN format(dateadd(day, -16, getdate()),'yyyy-MM-dd') 
         ELSE format(dateadd(day, -1, getdate()),'yyyy-MM-dd') END StartDate,
    FORMAT(dateadd(day, -1186, getdate()), 'MM/dd/yyyy') EndDate

But it raises a number of items that cumulatively lead me to question the quality of the original package in the first place (any 1-3 of these would be fine, but ALL of them together raises my eyebrows):

• Why the trailing / on file date?
• Why does the end date come before the start date?
• Why 1186 days, which has no significance relative to weeks, months, or years?
• Why go back 16 days (half a month) based on a weekday like Sunday, instead of a day of the month?
• Why repeat the entire TO_CHAR() expression inside the CASE, instead of just using the expression to produce the integer date offset?
• Why are we producing strings at all, instead of actual date objects, so the client code or report tool can format to fit the environment?