How to use the date from a FileName and use this date in an expression - SSIS Package

Question

I am quite a beginner with SSIS packages so bear with me. What I am trying to do:

I have daily files in the format of yyyy-mm-dd_filename_bla_bla.tsv The date of the file need to be added in the table were I am trying to import it. Currently I am doing this manually with a derived coloumn with the expression: (DT_DATE)(DT_DBDATE)"yyyy-mm-dd"

Is there a possibility to automatically take the file name and only take the date part to import it into the table.

The things I find on the internet is getting the date into the file name, but this is exactly the opposite.

I hope I provided enough information, and anyone can help me out with this problem.

thanks in advance.

For the current file name, do you have it in an SSIS Variable as just ` yyyy-mm-dd_filename_bla_bla.tsv` or does it have the fully qualified path `C:\ssisdata\Source\Input\yyyy-mm-dd_filename_bla_bla.tsv` — billinkc, Apr 01 '15 at 13:31

score 1 · Accepted Answer · answered Apr 01 '15 at 12:36

1

If you know the file name then keep that file name in a variable example let file name be : 01/02/2015_kjh.bgd

then by using derived column use string functions as left(@variable,10)

10-> length of date

then map it to your oledb destination

answered Apr 01 '15 at 12:36

koushik veldanda

1,079
10
23

1

did you get what am I saying – koushik veldanda Apr 01 '15 at 12:38
I did get what you are saying but this is static no? When using the same package for another file, with different date it wouldn't update? What I would like to get is to make it dynamic. example file: 2015-02-25.company.prod.sisense_data.tsv where date is changing. – J Bom Apr 01 '15 at 12:56
1

yes you have to use foreachloop container to get the names of the files in a specific folder and u will assign the name to the variable in container only – koushik veldanda Apr 01 '15 at 12:58
Right now I am using this expression: (DT_DATE)(DT_DBDATE)LEFT(@[User::CurrentFile],10) which does not seem to be actually storing anything to the database. (DT_DATE)(DT_DBDATE)"2015-02-16" does work however. What is it what I am doing wrong? The variable currrentfile does seem to be working though. – J Bom Apr 02 '15 at 11:20
are u able to convert it to date if so then map it to destination vial oledb dest – koushik veldanda Apr 02 '15 at 12:33

score 0 · Answer 2 · edited May 23 '17 at 10:24

To update/insert into a table you can use the SSIS SQL Exec task and pass in the variable value (which you know how to get) into the update/insert statement as a parameter. Its not hard just be careful on your settings of parameter names 0.

Here is a detailed description on how to do exactly what you are asking once you have a parameter set up: How to pass variable as a parameter in Execute SQL Task SSIS?

How to use the date from a FileName and use this date in an expression - SSIS Package

2 Answers2