SQL and SSIS : Extract part of file name in SQL / SSIS.

Question

Hi All - I have five SSIS packages and each of these packages source is a csv file. Each packages access the shared folder path respectively and loads the data into the SQL database table. Below are the file naming conventions of the files :

1. CoreJune2023 - Formatted for 24 month upload_2023-06-01
2. UpgradeJune_20230701_2023-06-01
3. July NonCore_0703 23_2023-06-01
4. July DP_0703 23_2023-06-01
5. DPlanJuly_20230630_2023-06-01
6. PlanJune_20230601_2023-06-01

Now the requirement is i need to grab the '2023-06-01' from the file name and store it into data table. sample below.

Could you please help me how to do it in SSIS package at variable level and also in SQL as well using functions ?

In SSIS, I am already the filename in the variable, may be i need to create another variable writing REVERSE ?

And if it is in SQL, i should write a SUBSTRING and REVERSE ?

When the SQL job runs calling the SSIS packages the file names are stored in the table along with the source path and archive path. Below is the information related to files stored in the separate table.

 ,[file_name]
      ,[file_path]
      ,[archive_file_path]
      ,[load_datetime]
      ,[load_status]

Answer 1

Hi @Hi @kkran,

Use token expression to do that.

TOKEN([yourcolumn], ".", 1)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @kkran,

We will use tokenization technique for both cases.

We need to retrieve last token from a string.

Here is how to do it via SSIS Expression:

TOKEN(@[User::fileName], "_", TOKENCOUNT(@[User::fileName], "_"))

Here is how to do it via T-SQL:

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, FileName VARCHAR(100));
INSERT INTO @tbl VALUES
('CoreJune2023 - Formatted for 24 month upload_2023-06-01'),
('UpgradeJune_20230701_2023-06-01'),
('July NonCore_0703 23_2023-06-01'),
('July DP_0703 23_2023-06-01'),
('DPlanJuly_20230630_2023-06-01'),
('PlanJune_20230601_2023-06-01');
-- DDL and sample data population, end
SELECT * 
, PARSENAME(REPLACE(FileName,'_','.'), 1)
FROM @tbl;

Answer 3

Hi @kkran,

In SSIS, you may use below expression.

RIGHT(@[User::Variable],FINDSTRING(REVERSE(@[User::Variable]),"_",1) - 1)

In SQL, you may use the code like below.

DECLARE @tbl TABLE(FileName VARCHAR(100))
INSERT INTO @tbl VALUES('CoreJune2023 - Formatted for 24 month upload_2023-06-01'),('UpgradeJune_20230701_2023-06-01')

SELECT REVERSE(LEFT(REVERSE(FileName),10))FROM @tbl 

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi @kkran,

Check the settings like below. You may need to set the parameter to match your variable.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Thanks @ZoeHui-MSFT . One last thing, the value is being captured like this in the table. how do i remove the .csv