Search code examples
datessisflat-file

SSIS - last day of month from an input date

I have a package that receives a date field from a flat file. Is there any quick transform this date to the last date of the same month?

e.g. Input: 01.08.2019 Output: 31.08.2019

Thanks a lot in advance

Solution

  • One way of doing it is via Lookup.

    Package -

    enter image description here

    So, basically you read the file then pass on them to Lookup which would run for each of them and query in the database to find out the last day of the month for the input date.

    You need OLEDB connection to be setup for the database queries and below SQL query would do the job for you - 

    DECLARE @date VARCHAR(10) = ?
SET @date = REPLACE(@date,'.','/');
SET @date = CONVERT(CHAR(10),CONVERT(DATETIME,LEFT(@date,10),105),101);
SET @date = EOMONTH(@date);
SET @date = SUBSTRING(@date, 9, 2) + '/' + SUBSTRING(@date, 6, 2)  + '/' + SUBSTRING(@date, 1, 4);
SELECT REPLACE(@date,'/','.') AS Result;