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 9:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 5,316, Visits: 12,347
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



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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #892941
Posted Tuesday, March 30, 2010 10:00 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:49 PM
Points: 132, Visits: 822
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.

Post #892981
Posted Tuesday, March 30, 2010 10:06 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,

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
Post #892989
Posted Tuesday, March 30, 2010 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 5,316, Visits: 12,347
Use a File System 'Move File' task.


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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #892990
Posted Tuesday, March 30, 2010 10:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, December 1, 2014 12:49 PM
Points: 132, Visits: 822
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.
Post #893017
Posted Tuesday, March 30, 2010 10:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 5,316, Visits: 12,347
?

You need to move everything apart from the latest file first - manually - before running the package.

From then on, assuming that you run this every day, all will be well.



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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #893037
Posted Thursday, November 1, 2012 1:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:58 AM
Points: 24, Visits: 74
Dave, I would like the expression for creating an expression based on a date.
First, based on today's date as I expect this would be simplest,
then based on a date parameter (I'm still reading about these).

Thanks!

Colleen aka Confused
Post #1379999
Posted Thursday, November 1, 2012 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:58 AM
Points: 24, Visits: 74
I'm following what you are saying about setting variables, etc.
I'm using variables to set different pieces of my connection string
"\\johnson\clientFTP\users\UBM\ATOM\GEN_V2_DAT_ENT_" + @[User::ShowIdentifier] + "_PRE_" + @[User::TodayDateOnly] + "_888888.txt"

Note that in this statement I'm using a UNC file path.
I'm getting the same error as Gandaghar about escape characters, so I can double up on the slashes. What about the double slash at the beginning? Make it three, or four slashes?

Nonfatal errors occurred while saving the package:
Error at ExportAll: The string literal "\\\johnson\\clientFTP\\users\\UBM\\ATOM\\GEN_V2_DAT_ENT_"" contains an illegal escape sequence of "\j". 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, "\\".


Colleen

Post #1380010
Posted Thursday, November 1, 2012 2:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:58 AM
Points: 24, Visits: 74
OK, I doubled up on the slashes within the path, and put four slashes at the beginning to indicate the UNC path. My expression evaluates to this.

\\johnson\clientFTP\users\UBM\ATOM\GEN_V2_DAT_ENT_IUXA9_PRE_20121101_888888.txt

I set the connection using this variable to delay validation as the file won't exist until I do the export. I set TextQUalifier to ", ColumnNamesInFirstDataRow to True, Format is Delimited, but don't see where to indicate that it is comma-delimited.
I set ValidateExternalMetadata in the DataFlow task to false - this file will be created at runtime.

I am getting this error (abbreviated)

Error at Export DAT [SSIS.Pipeline]: "component "Source - DAT view" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".


What can I do to make this work?

I'm using a variable to hold an expression that will set the file name at runtime then export data to the new file name.

Can you advise me?

Colleen





Post #1380021
Posted Thursday, November 1, 2012 2:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 5,316, Visits: 12,347
OK, I doubled up on the slashes within the path, and put four slashes at the beginning to indicate the UNC path. My expression evaluates to this.

\\johnson\clientFTP\users\UBM\ATOM\GEN_V2_DAT_ENT_IUXA9_PRE_20121101_888888.txt

I set the connection using this variable to delay validation as the file won't exist until I do the export. I set TextQUalifier to ", ColumnNamesInFirstDataRow to True, Format is Delimited, but don't see where to indicate that it is comma-delimited.
I set ValidateExternalMetadata in the DataFlow task to false - this file will be created at runtime.

I am getting this error (abbreviated)

Error at Export DAT [SSIS.Pipeline]: "component "Source - DAT view" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".



What can I do to make this work?

I'm using a variable to hold an expression that will set the file name at runtime then export data to the new file name.

Can you advise me?

Colleen


One way to get round this is to create a dummy version of the file eg

\\johnson\clientFTP\users\UBM\ATOM\template.txt

and put the column headings and (say) one row of data in there.

Point your connection at this. Of course, it will be overridden at runtime, but SSIS will still use it for meta data purposes at design time.



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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1380026
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse