InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 01:38:07 GMT20http://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxOMG - the source is a view and one column name changed.I set the ValidateExternalMetadata property to False and the error went away.Now I only get an error on the export file name [quote]Error at ExportAll [Connection manager "DAT export file"]: The file name "@[User::DATOutputFilePath]" specified in the connection was not valid.Error at ExportAll [Connection manager "DAT export file"]: The file name property is not valid. The file name is a device or contains invalid characters.[/quote]This is the value of the variable when running it. \\johnson\clientFTP\users\UBM\ATOM\GEN_V2_DAT_ENT_IUXA9_PRE_20121101_888888.txtThis is still an error related to using the dynamic connection, just seen more clearly.The file name looks good.The destination file doesn't exist, of course, because the file name will change every day.If the file has to exist (???) then it looks like I have to play the shell game - have two template files. Update one with data, rename it to the calculated name, then overwrite the now-dirty template file with the blank copy. It seems cludgy - too much file system work just to dump a new file.Any ideas? grasshopper aka SSIS noviceThu, 01 Nov 2012 16:33:18 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxThe dynamic connection is the destination - a flat file.Thu, 01 Nov 2012 16:20:41 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]cmcc (11/1/2012)[/b][hr]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.txtI 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)[code="plain"]Error at Export DAT [SSIS.Pipeline]: "component "Source - DAT view" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".[/code]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.[/quote]Is the dynamic connection your source or destination? The error you have posted is saying that the "Source - DAT view" is the problem - is that your source or are your connection names mixed up? If this is your source go into your data flow task and double click your source to update the metadata.MWiseThu, 01 Nov 2012 14:46:25 GMTMWisehttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote]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.txtI 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[/quote]One way to get round this is to create a dummy version of the file eg\\johnson\clientFTP\users\UBM\ATOM\template.txtand 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.Thu, 01 Nov 2012 14:15:28 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxOK, 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.txtI 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)[code="plain"]Error at Export DAT [SSIS.Pipeline]: "component "Source - DAT view" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".[/code]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?ColleenThu, 01 Nov 2012 14:04:35 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxI'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?[code="plain"]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, "\\".[/code]ColleenThu, 01 Nov 2012 13:45:47 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxDave, 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 ConfusedThu, 01 Nov 2012 13:25:12 GMTcmcchttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx?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.Tue, 30 Mar 2010 10:34:24 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxHi,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.Tue, 30 Mar 2010 10:21:42 GMTGangadhara MS http://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxUse a File System 'Move File' task.Tue, 30 Mar 2010 10:06:03 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]Gangadhara MS (3/30/2010)[/b][hr]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.[/quote]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 etcDaveTue, 30 Mar 2010 10:06:02 GMTDave Morrisonhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxHi,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.Tue, 30 Mar 2010 10:00:34 GMTGangadhara MS http://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]david.morrison-1035652 (3/30/2010)[/b][hr]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 :-)[/quote]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 backslashesTue, 30 Mar 2010 09:29:12 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxyou 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 :-)Tue, 30 Mar 2010 09:11:44 GMTDave Morrisonhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxThe two backslashes are causing the error. Surely just a single \ is all that you need?Tue, 30 Mar 2010 09:05:28 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxHi,My expression is giving an error can you please help me resolve this."C:\Documents and Settings\gangadharam\desktop\XMLSource\\"+ @[User::Datestring] +".xmlError: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.Tue, 30 Mar 2010 08:48:49 GMTGangadhara MS http://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]david.morrison-1035652 (3/30/2010)[/b][hr][quote][b]Phil Parkin (3/30/2010)[/b][hr]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.[/quote]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.[/quote]:-) 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.Tue, 30 Mar 2010 08:23:17 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]Gangadhara MS (3/30/2010)[/b][hr]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.[/quote]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)ThanksDaveTue, 30 Mar 2010 07:56:39 GMTDave Morrisonhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxHi 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.Tue, 30 Mar 2010 07:37:25 GMTGangadhara MS http://www.sqlservercentral.com/Forums/Topic892529-148-1.aspx[quote][b]Phil Parkin (3/30/2010)[/b][hr]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.[/quote]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.Tue, 30 Mar 2010 07:21:35 GMTDave Morrisonhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxI 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.Tue, 30 Mar 2010 07:13:37 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxHi 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 storedThen 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 columnThis will then dynamically set the connection manager to look for a file with todays date onHope this HelpsCheersDaveTue, 30 Mar 2010 07:04:10 GMTDave Morrisonhttp://www.sqlservercentral.com/Forums/Topic892529-148-1.aspxDear 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,GangadharTue, 30 Mar 2010 02:20:15 GMTGangadhara MS