locked
Importing .csv file where file name changes RRS feed

  • Question

  • Hi guys,

    I'm trying to import a csv file daily to my database where the file name changes daily, but only the date part does.

    Sessions_2014_0627.csv, Sessions_2014_0628.csv, etc

    What's the best way to go about importing this in SSIS

    Note: The old csv files will remain in the same folder.
    • Edited by Diango Monday, June 30, 2014 11:14 PM
    Monday, June 30, 2014 10:43 PM

Answers

  • Yea but if I do it that way, wouldn't it try to load the previous day csv file as well?  The previous day files will remain in the same folder as today's csv.

    Nope

    You can implement a filter logic as explained here to pick only the current days file. Just make sure you create a variable with current date in your required format and use it in script task as explained here to set the flag which would be used for expression based precedence constraint as below

    @CurrentDayFile == True
    http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Charlie Liao Thursday, July 3, 2014 9:47 AM
    • Marked as answer by Charlie Liao Monday, July 7, 2014 9:51 AM
    Tuesday, July 1, 2014 2:09 AM
  • Another way, you can evaluate the file name dynamically based on the variable expression and just pick the specific file

    example

    "c:\\temp\\Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    output : c:\temp\Sessions_2014_0701.csv

    Add a new SSIS variable to your package. Select the new variable in the Variables window and view properties (press F4). Set the EvaluateAsExpression property to True and enter the following for the Expression property value

    "Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    This expression uses functions to extract the month, day and year values, and includes the values to create the name based on today's date.

    Select the connection manager for your flat file. View properties (press F4). Select the Expressions property and click the elipsis (...) button. In the Property Expression Editor select the ConnectionString property. And enter the name of your new variable in the following format:

    @[User::MyVariable]

    If you need to include the path, create the expression like this:

    "C:\\temp\\" + @[User::MyVariable]

    If you prefer, you can avoid the variable and put the entire expression in the ConnectionString property:

    "C:\\temp\\" + "Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Tuesday, July 1, 2014 3:13 AM
    • Proposed as answer by Charlie Liao Thursday, July 3, 2014 9:47 AM
    • Marked as answer by Charlie Liao Monday, July 7, 2014 9:51 AM
    Tuesday, July 1, 2014 2:47 AM

All replies

  • I suggest using a for each loop control flow task, set it to enumerate files, look for files with names like Sessions*.csv, load the file name into a variable by a variable mapping. From there you're on the road.
    Tuesday, July 1, 2014 1:50 AM
  • Yea but if I do it that way, wouldn't it try to load the previous day csv file as well?  The previous day files will remain in the same folder as today's csv.
    Tuesday, July 1, 2014 2:02 AM
  • Yea but if I do it that way, wouldn't it try to load the previous day csv file as well?  The previous day files will remain in the same folder as today's csv.

    Nope

    You can implement a filter logic as explained here to pick only the current days file. Just make sure you create a variable with current date in your required format and use it in script task as explained here to set the flag which would be used for expression based precedence constraint as below

    @CurrentDayFile == True
    http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Charlie Liao Thursday, July 3, 2014 9:47 AM
    • Marked as answer by Charlie Liao Monday, July 7, 2014 9:51 AM
    Tuesday, July 1, 2014 2:09 AM
  • Another way, you can evaluate the file name dynamically based on the variable expression and just pick the specific file

    example

    "c:\\temp\\Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    output : c:\temp\Sessions_2014_0701.csv

    Add a new SSIS variable to your package. Select the new variable in the Variables window and view properties (press F4). Set the EvaluateAsExpression property to True and enter the following for the Expression property value

    "Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    This expression uses functions to extract the month, day and year values, and includes the values to create the name based on today's date.

    Select the connection manager for your flat file. View properties (press F4). Select the Expressions property and click the elipsis (...) button. In the Property Expression Editor select the ConnectionString property. And enter the name of your new variable in the following format:

    @[User::MyVariable]

    If you need to include the path, create the expression like this:

    "C:\\temp\\" + @[User::MyVariable]

    If you prefer, you can avoid the variable and put the entire expression in the ConnectionString property:

    "C:\\temp\\" + "Sessions_"+ (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) +"_"+ RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2)  + ".csv"

    Thanks

    Saravana Kumar C


    • Edited by SaravanaC Tuesday, July 1, 2014 3:13 AM
    • Proposed as answer by Charlie Liao Thursday, July 3, 2014 9:47 AM
    • Marked as answer by Charlie Liao Monday, July 7, 2014 9:51 AM
    Tuesday, July 1, 2014 2:47 AM
  • Sweet!  Thanks guys!
    Tuesday, July 1, 2014 1:44 PM
  • I do this each and every day of the week.  Here's my sample code.

    declare @fullpath1 varchar(1000)
    select @fullpath1 = '''\\system.local\london\FTP\' + convert(varchar, getdate()-1, 112) + '_Daily.SPL'''
    declare @cmd1 nvarchar(1000)
    select @cmd1 = 'bulk insert [dbo].[Daily_File] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
    exec (@cmd1)
    
    

    This the previous day's file and loads it into a table.

    This is a great site for date formats.

    http://www.sql-server-helper.com/tips/date-formats.aspx


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, July 15, 2014 10:17 PM