How to use if else with flat file connection Expression in SSIS?

0
votes

I have one SSIS which takes data from .csv file and insert into sql table

now my file name is

Flat file properties and expression for connection string is :

@[User::Directory] + @[User::Filename]+" "+(DT_STR,4,1252)DATEPART( "yyyy" , getdate() ) +"-"+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +"-"+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , DATEADD("day", -1, GETDATE()) ), 2) +".csv"

which gives me filename like : C:\mytempdata\ACCTMVCHNG_CSV 2011-09-18.csv

this is yesterdays filename

but when i run this package on monday the file present is for friday only name will be ACCTMVCHNG_CSV 2011-09-16.csv

so i want to use if else into this expression

like if todays day is monday then take fridays file i mean create file path for friday

like this : C:\mytempdata\ACCTMVCHNG_CSV 2011-09-16.csv

or else take as it is yesterdays like what i'm doing in above expression...

1
ssis
help me all what happen don't have answer :(? - Neo

1 Answers

1
votes

If I understand you correctly on Tuesday you want the file created on Monday. On Wednesday you want the file created on Tuesday. But on Monday you don't want the file created on Sunday but on Friday.

@[User::Directory] + @[User::FileName]+" "+
(DT_STR,4,1252)DATEPART("yyyy", getdate() ) +"-"+ 
RIGHT("0" + (DT_STR,4,1252)DATEPART("mm", getdate() ), 2) +"-"+ 
RIGHT("0" + (DT_STR,4,1252)DATEPART("dd", 
    DATEADD("day", DATEPART("dw", GETDATE()) == 1 ? -3 : -1, GETDATE()) ), 2) +".csv"

Try this expression. It extends yours with a condition. It uses the DATEPART function again with the "dw" parameter which returns the ordinal number of the current day of the week. Play with this. (In my case monday is the 1st day of week) If the current day is Monday, the DATEADD function add -3 days.