Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Latest file picking from the folder


Latest file picking from the folder

Author
Message
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
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
Dave Morrison
Dave Morrison
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 242
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:BigGrinateString] + ".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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8360 Visits: 19493
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Dave Morrison
Dave Morrison
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 242
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.
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
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.
Dave Morrison
Dave Morrison
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 242
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8360 Visits: 19493
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
Hi,
My expression is giving an error can you please help me resolve this.

"C:\Documents and Settings\gangadharam\desktop\XMLSource\\"+ @[User:BigGrinatestring] +".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:BigGrinatestring] +".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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8360 Visits: 19493
The two backslashes are causing the error. Surely just a single \ is all that you need?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Dave Morrison
Dave Morrison
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 242
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 :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search