SSIS Derived Column - How to convert substring to date?

1
votes

I'm using the SSIS Derived Column transformation. The source of the field is the Filename that's saved in the flat file source. Thefully-qualified UNC path is

\\v201.os33cust.net\TKFMC\groups\Managed Care Operations\Health Plan Specific Data\Self-Funded\MedImpact\Accumulator\Download\Reload\type28_phi01_ais_02032021_02032021_001.txt

Since the filename is a UNC path, and I just need the date portion of it in yyyy-mm-dd format, I wrote the following expression in the derived column transformation:

(DT_DBDATE)(SUBSTRING(Filename,155,4) + "-" + SUBSTRING(Filename,151,2) + "-" + SUBSTRING(Filename,153,2))

The initial processing succeeded a few days ago. Today, an error is thrown stating:

An error occurred while attempting to perform a type cast.

The destination field for the above expression is in SQL Server 2016 using the date field type. The reason the date is formatted as yyyy-mm-dd is because the format is needed further downstream when I produce a flat file requiring this format.

Since the process succeeded a few days ago, and nothing has changed with regard to environment, table etc., I'm curious to know how best to resolve this issue?

1
sql-serverssis
Check the file names, maybe the format changed so your parsing of the date does not work because the file name format changed. Also if it stopped today but worked yesterday, maybe your date was only accounting for 1 digit for day (so 2/9/21 yesterday but 2/10/21 today) so extra character you may not have taken into count. – Brad
Please share the fully qualified file path value of the Filename. – Yitzhak Khabinsky

1 Answers

1
votes

UNC

\\v201.os33cust.net\TKFMC\groups\Managed Care Operations\Health Plan Specific Data\Self-Funded\MedImpact\Accumulator\Download\Reload\type28_phi01_ais_02032021_02032021_001.txt

It is very dangerous to rely on the SUBSTRING() function while processing UNC paths because they flactuate. SSIS has much better functions for your scenario:

  • TOKEN()
  • TOKENCOUNT()

In the following example, the TOKEN function returns the file name from the specified path:

TOKEN(@[User::fileNameUNC], "\\", TOKENCOUNT(@[User::fileNameUNC], "\\"))

So you will have type28_phi01_ais_02032021_02032021_001.txt at this point.

You would need to repeat the same approach while using underscore character to tokenize it. The file name contains 6 tokens. It seems that you need a token #4.

TOKEN(TOKEN(@[User::fileNameUNC], "\\", TOKENCOUNT(@[User::fileNameUNC], "\\")), "_", 4)

So you will have 02032021 at this point.

And just after that use the SUBSTRING() function to compose a DATE.

Here is your full expression:

SUBSTRING(TOKEN(TOKEN(@[User::fileNameUNC], "\\", TOKENCOUNT(@[User::fileNameUNC], "\\")), "_", 4), 5,4)
+ "-" +
SUBSTRING(TOKEN(TOKEN(@[User::fileNameUNC], "\\", TOKENCOUNT(@[User::fileNameUNC], "\\")), "_", 4), 1,2)
+ "-" +
SUBSTRING(TOKEN(TOKEN(@[User::fileNameUNC], "\\", TOKENCOUNT(@[User::fileNameUNC], "\\")), "_", 4), 3,2)

in VS: enter image description here