locked
Dynamically select Earliest date time Flat file RRS feed

  • 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


    • Edited by ravi3005 Tuesday, October 11, 2011 4:53 PM
    Thursday, September 15, 2011 2:20 PM

Answers

  • 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?


    • Edited by BIRocks Thursday, September 15, 2011 9:26 PM
    • Marked as answer by ravi3005 Friday, September 16, 2011 7:08 PM
    Thursday, September 15, 2011 9:24 PM

All replies

  • 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.
    Thursday, September 15, 2011 2:30 PM
  • 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/

    Thursday, September 15, 2011 2:36 PM
  • Thanks for the Reply.

     

    What kind of conditional expression i can use??

    Thursday, September 15, 2011 4:30 PM
  • 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?
    Thursday, September 15, 2011 5:17 PM
  • yes ur right..thats it i want

     

    how can i do that??

    Thursday, September 15, 2011 6:01 PM
  • 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.


    • Edited by BIRocks Thursday, September 15, 2011 6:45 PM
    Thursday, September 15, 2011 6:16 PM
  • I cant the second Picture in Your reply Can u Poast it again..

     

    Thank You

    Thursday, September 15, 2011 6:26 PM
  • 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

    Thursday, September 15, 2011 6:39 PM
  • 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 

    Thursday, September 15, 2011 7:53 PM
  • Actually i didnt understand the 3rd step.can you please expain that .

     

    Thank you

    Thursday, September 15, 2011 8:09 PM
  • 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?


    • Edited by BIRocks Thursday, September 15, 2011 9:26 PM
    • Marked as answer by ravi3005 Friday, September 16, 2011 7:08 PM
    Thursday, September 15, 2011 9:24 PM