March 30, 2010 at 2:20 am
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.(Data flow task with source XML and OLEDB destination).
I will be having the file name like 20100328,20100329,20100330 etc.
Please help.
Thanks,
Gangadhar
March 30, 2010 at 7:04 am
Hi Gangadhar,
This can be acheived fairly simply using two variables.
Firstly Create one variable called something like DateString or similar. Set its EvaluateAsExpression property to true and enter an expression that will build a string for todays date in the format you haver stated, YYYYMMDD? (Ask if you want me to give you the expression for this)
Then create a second variable called FilePath or something similar and again set its EvaluateAsExpression property to true and set its expression to "<PATH TO FILE>\\" + @[User::DateString] + ".xml"
Obviously replacing <PATH TO FILE> with a path relative to your package where the files will be stored
Then in the connetion manager for the xml source file view the properties, in there click the three dotted elipse button on the "Expressions" property. In the box that pops up set select "ConnectionString" in the property column and type @[User::FilePath] in the Expression column
This will then dynamically set the connection manager to look for a file with todays date on
Hope this Helps
Cheers
Dave
March 30, 2010 at 7:13 am
I do not believe that this is the most efficient way of working.
I get the impression that most people here move files to an archive folder once they have been processed. If you always do this, you won't need to put file-select logic into your package, and that is how I would recommend you proceed.
You will also benefit from knowing that everything in the archive folder has, in fact, been loaded. You would not easily know this otherwise.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 7:21 am
Phil Parkin (3/30/2010)
I do not believe that this is the most efficient way of working.I get the impression that most people here move files to an archive folder once they have been processed. If you always do this, you won't need to put file-select logic into your package, and that is how I would recommend you proceed.
You will also benefit from knowing that everything in the archive folder has, in fact, been loaded. You would not easily know this otherwise.
I'm going to disagree with you here Phil, this approach adds a element to the processing in the form of renaming the file to a common file name and also copying it to an archive (which is either a bunch of clumsy file system tasks or a manual process). With this method you would also need to the re name the file on the way into the archive so you know which days file it was.
March 30, 2010 at 7:37 am
Hi David,Thanks for reply.
From where i can set EvaluateAsExpression property to true in the variable and pleas edo send me the expression as well..
as i am new to SSIS ..i will help a lot for me..
Thanks David.
March 30, 2010 at 7:56 am
Gangadhara MS (3/30/2010)
Hi David,Thanks for reply.From where i can set EvaluateAsExpression property to true in the variable and pleas edo send me the expression as well..
as i am new to SSIS ..i will help a lot for me..
Thanks David.
Hi Gangadhara,
You set the EvaluateAsExpression propert by selecting the variable and then looking in the properties window, its an option there.
The formaule for building the date string in YYYYMMDD format is as below. I urge you to look at it, pull it apart and figure out what its doing rather than just blindly using it as this will help further your understanding
(DT_WSTR, 4) year(getdate())
+ right("0" + (DT_WSTR, 2) month(getdate()), 2)
+ right("0" + (DT_WSTR, 2) day(getdate()), 2)
Thanks
Dave
March 30, 2010 at 8:23 am
david.morrison-1035652 (3/30/2010)
Phil Parkin (3/30/2010)
I do not believe that this is the most efficient way of working.I get the impression that most people here move files to an archive folder once they have been processed. If you always do this, you won't need to put file-select logic into your package, and that is how I would recommend you proceed.
You will also benefit from knowing that everything in the archive folder has, in fact, been loaded. You would not easily know this otherwise.
I'm going to disagree with you here Phil, this approach adds a element to the processing in the form of renaming the file to a common file name and also copying it to an archive (which is either a bunch of clumsy file system tasks or a manual process). With this method you would also need to the re name the file on the way into the archive so you know which days file it was.
🙂 and I will disagree right back at you. Use a FOREACH container and Expressions to avoid the common file name issue and process files with dynamic names. The only file-system task required is a 'move' at the end of processing. No need to rename - the file already has date as part of its name.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 8:48 am
Hi,
My expression is giving an error can you please help me resolve this.
"C:\Documents and Settings\gangadharam\desktop\XMLSource\\"+ @[User::Datestring] +".xml
Error:
The string literal "C:\Documents and Settings\gangadharam\desktop\XMLSource\\"" contains an illegal escape sequence of "\D". The escape sequence is not supported in string literals in the expression evaluator. If a backslash is needed in the string, use a double backslash, "\\".
Attempt to parse the expression ""C:\Documents and Settings\gangadharam\desktop\XMLSource\\"+ @[User::Datestring] +".xml" failed and returned error code 0xC00470B8. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
March 30, 2010 at 9:05 am
The two backslashes are causing the error. Surely just a single \ is all that you need?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 9:11 am
you need to put double backslashes in all paths I've found as ssis treats a single backslash as an escape character, you double them up to aviod this.
Phil, I like your FOREACH suggestion, although surely this would be better for multiple files?
We're essentially doing the same thing just different ways, the only differnce being I didnt suggest to put a file move task at the end 🙂
March 30, 2010 at 9:29 am
david.morrison-1035652 (3/30/2010)
you need to put double backslashes in all paths I've found as ssis treats a single backslash as an escape character, you double them up to aviod this.Phil, I like your FOREACH suggestion, although surely this would be better for multiple files?
We're essentially doing the same thing just different ways, the only differnce being I didnt suggest to put a file move task at the end 🙂
It was designed for multiple files, you are correct.
But it's an easy way of getting around the common "I want the package to complete successfully if the source file is not found" requirement, for those who are averse to writing code.
Obviously, in this case, the properties of the container are set such that only the specific file of interest can ever be selected and no iteration is possible, should other files somehow find their way into the folder unexpectedly.
--edit - good spot with the backslashes
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 10:00 am
Hi,
Should i use a File transfer task at the end to move the inserted file to another folder.
My initial requirement was in the common folder i will receive dially 1 XML file from FTP, i need to insert that latest XML file into DB.thats it.
March 30, 2010 at 10:06 am
Gangadhara MS (3/30/2010)
Hi,Should i use a File transfer task at the end to move the inserted file to another folder.
My initial requirement was in the common folder i will receive dially 1 XML file from FTP, i need to insert that latest XML file into DB.thats it.
Hi Gangadhara,
No you dont need to do this but its certainly not bad practise either. As Phil wrote this will give you a quick visual indicator at file level as to what has been imported and not.
Its more about what you are comfortable with / what time allows in devleopment etc etc
Dave
March 30, 2010 at 10:06 am
Use a File System 'Move File' task.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 30, 2010 at 10:21 am
Hi,
I used a For each loop container and i followed all you mentioned step,but the dataflow task is inserting all the XML file records into destination table.
Please help me to fetch only one latest file.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply