and this is a lot easier to do in Powershell - which could be called from SSIS directly if "boss" insists in SSIS

variables could be passed from ssis or...

Hi @Andy you remember the environment you had help me migrate? well, that whole server and it backup infrastructure just failed drastically. We were able to recover the SSISDB....

Have you also considered FileTables for this?

Thanks, Jacob, for the correction.

The fact that the result returned by

SELECT CAST('YYYY-MM-DD' AS date);

is language dependent, and therefore non-deterministic, is enough reason for me to use the 'YYYYMMDD' format, for which the above expression...

If it were me, I think I'd resort to a script component, as there are too many nested IFs for my liking.

However, things could be made much easier on the...

you can install OpenXML SDK and use that to create Excel Files without the need for Office to be installed.

But yes it does require coding in C# to...

As far as I know (and I will happily be proved wrong), programmatic formatting of Excel sheets requires the installation of the Excel application. Without that, you do not have...

For someone with...

This is on 2017.

Can someone else please test on 2019? Or maybe explain why it would not work there?

I started out down this path, but found that carving out all of the individual components resulted in an even longer...

Are you saying that my code 'does not work'? If so, please provide...

I'm sure that this could be simplified with a bit more thought, but this works for the example provided:

DECLARE @Text NVARCHAR(250) = N'CTranTyp-ViewId_2020.03.24_13h05min10s.xls';

SELECT
    ...

OK, so my code should work, with some refinements to handle the zeros.

DECLARE @DateStr NVARCHAR(50) = N'12172019';
SELECT @DateStr,
    CAST(CONCAT(RIGHT(@DateStr, 4), LEFT(@DateStr, 2), SUBSTRING(@DateStr, 3, 2)) AS DATE);

• This reply was modified 5 years, 11 months ago by Phil Parkin.