| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-03-29 : 04:11:43
|
| Dear all,I am using the XML data source while is daily downloaded from FTP and placed in one common folder.I need to pick the latest file which is downloaded from FTP for daily insertion of data to destination table.Please suggest me how can we do this.Thanks for your help in advance,Gangadhar |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-29 : 04:57:15
|
| you can use a FOR EACH LOOP container with file enumerator to look for your target folder. Then use a variable inside SSIS to capture filename of files found and do a check to identify latest ones and add a data flow task inside for transfering them to destination table using xml source and oledb destination.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-03-29 : 05:23:05
|
| Hi Visakh,Thanks for your prompt reply.Can u please tell me how we can check or identify the latest ones ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-29 : 06:15:47
|
| Dont you've timestamp value added to your filename?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-03-29 : 07:21:48
|
| Yes i do have if that is the case how do i specify the latest file name to pick only,,? |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-03-29 : 09:35:49
|
| Can any body help me how to pick the latest file which came to folder. I will be having the file name like 20100328,20100329,20100330 etc.Please help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-29 : 12:17:57
|
quote:
Originally posted by gangadhara.ms
Can any body help me how to pick the latest file which came to folder. I will be having the file name like 20100328,20100329,20100330 etc.Please help.
Inside your foreachloop file enumerator add a Script task and inside this pass the filename variable as well as your package run date obtained from @[System::PackageStartTime]. then look inside the filename to see if date is present using filename.IndexOf() function. get result as a boolean value (true if found, false otherwise) and store it in a boolean variable created @[User::ResultVariable]. Add a data flow task following this with XML source pointing to your XML file variable and OLEDB destination which is your table. Add a precedence constraint from Script task to DFT and choose constraint AND expression option and write expression as @[User::ResultVariable] ==true. This will make sure DFT gets executed only if file name contains run date (ie recent file)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-03-30 : 08:08:20
|
| Still no luck ..OK i will opt for this option of moving to another folder ones its inserted into database, i need to move to archive folder.The source will be XML source how do we do this ? I used with File system task its not copying more than 1 file.Thanks |
|
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-03-30 : 08:18:07
|
| This is the error ADDITIONAL INFORMATION:Error at File System Task [File System Task]: Type of Connection "XML Source" is not a file.Error at File System Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-30 : 14:04:04
|
quote:
Originally posted by gangadhara.ms
Still no luck ..OK i will opt for this option of moving to another folder ones its inserted into database, i need to move to archive folder.The source will be XML source how do we do this ? I used with File system task its not copying more than 1 file.Thanks
wats the purpose of using file system task? as per your initial explanation what you wanted was Data flow task with xml source and oledb destination------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Latest file picking from the folder