March 30, 2010 at 10:34 am
?
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 1, 2012 at 1:25 pm
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
November 1, 2012 at 1:45 pm
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
November 1, 2012 at 2:04 pm
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
November 1, 2012 at 2:15 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 1, 2012 at 2:46 pm
cmcc (11/1/2012)
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.
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.
MWise
November 1, 2012 at 4:20 pm
The dynamic connection is the destination - a flat file.
November 1, 2012 at 4:33 pm
OMG - 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
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.
This is the value of the variable when running it.
\\johnson\clientFTP\users\UBM\ATOM\GEN_V2_DAT_ENT_IUXA9_PRE_20121101_888888.txt
This 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 novice
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply