SQL Clone
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

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

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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 840
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.
Dave Morrison
Dave Morrison
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 242
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

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

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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 840
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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50733 Visits: 21152
?

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.

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.
cmcc
cmcc
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 75
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
cmcc
cmcc
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 75
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
cmcc
cmcc
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 75
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

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

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.
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