get latest file by ssis - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

get latest file by ssis

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 07:50:21

hi
i have folder with files ,in format file_ddmmyyyy hh:mm:ss

I am trying to find a way within SSIS to only get/load the latest file, based on the date and time of the file.

how can i find the newest file?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:22:07

parse the files using a for each loop file enumerator
use a execute sql task inside the loop to log file details to a control table
then extract date portion of filename to datetime column in same table.
in another sql task take filename corresponding to latest datevalue from table and store it in a variable created in ssis
add a data flow task with flat file source and map connection string to above variable
add your destination and link file source to it

if you dont want log data anymore have a step to purge table data at the end of package outside the loop

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 13:11:49

what do you mean "to log file details to a control table"
take the name of the file to the table?how can i do it?
suppose i have some files

file1_20120102 15:01.xls
file1_20120102 15:02.xls
file1_20120102 15:03.xls
in loop
1.in table i put the file name in one column
2.in table i put the date in second column
3.afetr the loop ,find the latest file in the table by the colum two(date)
???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 13:31:26

yep exactly. after puting filename and date in table which is in execute sql task
you can add another execute sql task to get filename of latest file using below query

SELECT TOP 1 filename
FROM LogTable
ORDER BY datefield DESC

then assign this value to variable and use it in next data flow task

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 14:18:54

how i insert the filesname to the table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 14:31:24

you need to create a variable and use it inside foreach loop to map filename attribute. It will automatically get filename each time loop iterates over a file

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 14:52:54

this i understand,
but how i insert the variable to table?

in execute sql task can i write?

INSERT INTO Lotable
SELECT @FileNme

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 14:55:06

nope

you can write like

INSERT INTO Lotable(?)

then map variable in parameter mapping tab

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 16:16:54

are you sure this syntax
INSERT INTO Lotable(?)

i get the error:
[Execute SQL Task] Error: Executing the query "INSERT INTO Logtable(?)" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

in parameter mapping:
variable Name:User::LastFileName
Direction:Input
DataType:VARCHAR
ParameterName:@LastFile
Parameter Size:255

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-02 : 16:24:40

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 18:22:30

missed values

INSERT INTO Lotable VALUES(?)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-03 : 07:00:44

i think that something is wrong with my component "ForEach loop container"

i do not see that the data of variable (fileName) is change in watch window.

inbs
Aged Yak Warrior

860 Posts

Posted - 2012-02-03 : 07:11:48

never mind it is working ,i can load the files to table
i go to the next step

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources