Dynamically select Earliest date time Flat file

Question

i have a flat files generated everyday night  by format  YYYYMMDD  HHMMSS_01A.txt

my task is that i have to create a package that select todays dateTime  file .

For that Am using some thing like this ..

"//xxxxxxxx\\xxxxx\\"+(DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" "+
RIGHT("0"+(DT_WSTR,2)DATEPART("hh",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("mi",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("ss",getdate()),2)+
"_01A.txt"

But the Problem is that it is selecting the time which am executing the package like

//jls-devprescnt\Inbound\20110914 091710_01A.txt

but the file is in //jls-devprescnt\Inbound\20110915 60336_01A.txt

I want package to select the file regardless of date and time but with of file name i.e   _01A.txt

can anyone please explain me in detail how to do that

Answer 1

your approach is not correct you are making expression to get filename based on the getdate for YYMMDD its fine but for HHMMSS obviously it'll not work

use a script task inside that compare all file names  and select latest based on the file name and in script task only build the connection string as you are building in expression after getting latest file name.

or you can approach as mention in this thread

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e868c6ac-1b08-47cd-b14e-2e4e7b511ccb

---

Shailesh , Please mark the post as answered if it answers your question.

Answer 2

Hello Ravi,

You can loop over the files in the respective directory and compare the names using script task in that loop and set the connection accordingly.

Else you can even put conditional expression, lets say you have a variable holding the file name  then..

([FileName] == (DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" )? [FileName] else " somethige else"

 

Regards,

Kutub

http://letslearnssis.blogspot.com/

Answer 3

Thanks for the Reply.

 

What kind of conditional expression i can use??

Answer 4

Ravi

Let's say you have 4 f.txt files for this week (i.e from Mon to Thu)

 

(1) //jls-devprescnt\Inbound\20110912 091710_01A.txt -- Mon

(2) //jls-devprescnt\Inbound\20110913 091710_01A.txt -- Tue

(3) //jls-devprescnt\Inbound\20110914 091710_01A.txt -- Wed

(4) //jls-devprescnt\Inbound\20110915 091710_01A.txt -- Thu

Now if you are running your ssis package today then you want to select only today's .txt file (No. - 4 ,Thu) Am I right?

Answer 5

yes ur right..thats it i want

 

how can i do that??

Answer 6

Ravi

 

Here's what I did

(1) In the first Execute SQL task I populate one variable @msdn_YYYYMMDD with today's date. (Set Result Set to single row, put the following query in the SQL Statement and in the Result Set mapping map @msdn_YYYYMMDD variable and Result Name = 0)

 

SELECT CONVERT(VARCHAR(20),GETDATE(),112)

 

(2) Then in the For Each Loop I have the connection folder set to .txt files. In your case it should be something like this:

Then each .txt file is enumerated and the path is stored in the variable called msdn_FEL_getFileName. (Variable Mapping in the For Each Loop)

(3) Then in the Execute SQL Task I extract YYYYMMDD part of the txt file name from the variable msdn_FEL_getFileName and compare it to Today's YYYYMMDD. If that matches then populate the final msdn_FilePath variable with matched file name (which will be //jls-devprescnt\Inbound\20110915 091710_01A.txt since your are running package today). Here is the SQL Script I uesd in Execute SQL Task:

 

DECLARE @str VARCHAR(100)
SET @str = ?

DECLARE @YYYYMMDD VARCHAR(8)
SET @YYYYMMDD = ?

DECLARE @FileName VARCHAR(100)

SELECT @FileName = LTRIM(RTRIM((REVERSE(SUBSTRING(REVERSE(@str), CHARINDEX('\',REVERSE(@str))-8,8)))))

SELECT CASE 
		WHEN @FileName = @YYYYMMDD
		THEN @str
		ELSE NULL
		END AS FilePath

 

(4) Then have the Data Flow Task after the For Each Loop --> Flat File Source --> Flat File Connection manager : For the first time you have to browse to the actual .txt file. Then in the properties of the Flat File Connection Manager --> Expression : ConnectionString and have @[User::msdn_FilePath] variable (the FilePath name populated after matching with Today's YYYYMMDD : Step - 3)

(5) And then do whatever transformation you have to do and dump the data to any destination.

Answer 7

I cant the second Picture in Your reply Can u Poast it again..

 

Thank You

Answer 8

It only lets me upload 2 pics so couldn't upload all. Here I have all screen shots uploaded to SkyDrive.

https://skydrive.live.com/?cid=4173b91c2679f92a&id=4173B91C2679F92A%21111&sff=1

Answer 9

Total we got Three variables here right...i dnt understand where to match which one..

If u dnt mind can u send me each and every screen shot and can u name them seperatly..

Actually am confused with variable mappings and value of variables

sorry for that..am completely new to ssis.

 

Thank You 

Answer 10

Actually i didnt understand the 3rd step.can you please expain that .

 

Thank you

Answer 11

Step-3:

(1) Drag and drop Execute SQL task inside the ForEach Loop. Double Click. On General Tab: Define the connection. Set ResultSet to Single Row.

(2) In the SQL Statement put this script:

DECLARE @str VARCHAR(100)
SET @str = ?

DECLARE @YYYYMMDD VARCHAR(8)
SET @YYYYMMDD = ?

DECLARE @FileName VARCHAR(100)

SELECT @FileName = LTRIM(RTRIM((REVERSE(SUBSTRING(REVERSE(@str), CHARINDEX('\',REVERSE(@str))-8,8)))))

SELECT CASE 
		WHEN @FileName = @YYYYMMDD
		THEN @str
		ELSE NULL
		END AS FilePath

(3) On the Parameter Mapping you have to have 2 parameters;

a) User::msdn_FEL_getFileName --> This parameter is the one you defined in the ForEach Loop properties (Step - 2). This variable  holds  .txt file path from the //jls-devprescnt\Inbound\  location.

 b) User::msdn_YYYYMMDD --> This parameter is from the Step -1 Execute SQL Task which populates the variable with YYYYMMDD with Today's date. (e.g. 20110915 for today). This parameter will be used to check the YYYYMMDD part of the file name   from User::msdn_FEL_getFileName.

Map these parameter in this Order only.

(4) In the ResultSet have the variable User::msdn_FilePath which will be populated with the file name having today's YYYYMMDD.

All the Parameters defined in SSIS package are type STRING (VARCHAR).

Did this make sense now?