Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Latest file picking from the folder Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 2:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:17 PM
Points: 132, Visits: 812
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

Post #892529
Posted Tuesday, March 30, 2010 7:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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
Post #892720
Posted Tuesday, March 30, 2010 7:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #892729
Posted Tuesday, March 30, 2010 7:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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.


Post #892744
Posted Tuesday, March 30, 2010 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:17 PM
Points: 132, Visits: 812
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.
Post #892758
Posted Tuesday, March 30, 2010 7:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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
Post #892771
Posted Tuesday, March 30, 2010 8:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #892822
Posted Tuesday, March 30, 2010 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 10:17 PM
Points: 132, Visits: 812
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.

Post #892866
Posted Tuesday, March 30, 2010 9:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #892896
Posted Tuesday, March 30, 2010 9:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:48 AM
Points: 92, Visits: 201
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

Post #892904
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse