SSIS - 用于FTP组件动态文件名 [英] SSIS - Dynamic File Name for FTP Component

问题描述

我试图创建SSIS数据流来帮助我通过FTP上传文件，我有一个有点麻烦。该文件的名称需要是动态的，在filename_mmddyy.xls的格式。 。因此，每天它适用的日期和上传新文件

我是能够让这个文件将正确保存在下面的表达式中的数据流：

  @ [用户::路径] + 
右键（0+（DT_STR，4,1252）DATEPART （M，GETDATE（）），2）+ 
右键（0+（DT_STR，4,1252）DATEPART（D，GETDATE（）），2）+ 
右键（ 0+（DT_STR，4,1252）DATEPART（YYYY，GETDATE（）），2）+ 
的.xls
  

的FTP组件，但不会采取这种作为localPath一个表达式。我试图覆盖我的用户::路径变量脚本部件的内部在我原来的数据流，但似乎没有任何工作。

我知道我失去了一些东西每个简单，但我已经在它足够的呆望了许久，它只是不来找我。

谢谢！

修改

好了，打打闹闹的另一天之后，这里是什么我已经出来了：

我做我的原始任务创建我的平面文件。侧面说明，我的供应商实际需要的真正的Excel的XLS，而不是一个CSV，所以我不得不做出一个文件连接目的地，然后要求我，使我的服务器上的静态文件。

然后我用一个脚本任务（C＃）的静态文件复制到文件的动态域名。当然，我不得不离开原来的文件，因为Excel目标需要有存在。

最后，我做了另一个文件连接和使用上述表达，因为它是连接串。然后，我有FTP任务使用文件连接，上传的文件。

我要去尝试和修剪它全部下来一点。我想，我可以不使用脚本任务，而是使用文件系统任务复制并使用该文件的连接，以帮助动态路径名脱身。

底行我学到的是：在SSIS中，当它说：PathIsVariable，就摆在那的文本字段是什么作为一个变量。所以，如果我把@ [用户::路径]，它解析为C：\pathonmydrive。 SSIS然后查找与该名称，例如：C变量：\pathonmydrive，找到该文件。这是一个有点反intuivite，但现在，我知道我能避免这种陷阱。

解决方案

1. 创建一个新的变量，例如，文件路径。使用字符串作为数据类型和变量的属性，设置 EvaluateAsExpression 真


2. 配置变量的表达式： @ [用户::路径] +filename_+右（0+（DT_STR，2,1252）DATEPART（M，GETDATE（）），2）+右（0+（DT_STR ，2,1252）DATEPART（D，GETDATE（）），2）+右（0+（DT_STR，4,1252）DATEPART（YY，GETDATE（）），2）+的.xls


3. 使用表达式编辑器中的评估表达按钮，查看是否表达式解析为正确的路径该文件。


4. 修改你的 FTP任务的表达式属性并集您在的localPath 创建的变量。

I'm trying to create a data flow in SSIS to help me upload a file via FTP and I'm having a bit of trouble. The name of the file needs to be dynamic, in the format of filename_mmddyy.xls. So every day it applies the date and uploads a new file.

I was able to make it so that the file would save correctly in a data flow with the following expression:

@[User::path]+ 
Right("0"+(DT_STR,4,1252)DatePart("m",getdate()),2)+
Right("0"+(DT_STR,4,1252)DatePart("d",getdate()),2)+
Right("0"+(DT_STR,4,1252)DatePart("yyyy",getdate()),2)+
".xls"

The FTP component, however, won't take this as an expression for the LocalPath. I tried to overwrite my User::path variable inside of a Script Component in my original data flow, but that didn't seem to work either.

I know I'm missing something every simple, but I've stared at it long enough and it's just not coming to me.

Thanks!

EDIT

Ok, so after fooling around for another day, here is what I've come up with:

I made my original task to create my flat file. Side note, my vendor actually needed a true excel xls and not a csv, so I had to make a file connection destination, which then required me to make a static file on my server.

I then used a script task (C#) to copy the static file to a file with the dynamic name. Of course, I have to leave the original file because the excel destination needs to have that there.

Finally, I made another file connection and used the above expression as it's connection string. I then have the FTP task use the file connection as the file to upload.

I'm going to try and trim it all down a bit. I think I can get away with not using the script task and instead using a file system task to copy and using the file connection to help with the dynamic path name.

Bottom line I've learned is this: In SSIS, when it says "PathIsVariable", what you put in that text field is taken as a variable. So if I put in @[User::path], it resolves to "C:\pathonmydrive". SSIS then looks for a variable with THAT name, ie C:\pathonmydrive, to find the file. It's a little counter-intuivite, but now that I know I can avoid that pitfall.

解决方案

1. Create a new variable, e.g., FilePath. Use string as data type and in the variable's properties, set EvaluateAsExpression to true.
2. Configure the expression of the variable: @[User::path] + "filename_" + Right("0"+(DT_STR,2,1252)DatePart("m",getdate()),2) + Right("0"+(DT_STR,2,1252)DatePart("d",getdate()),2)+ Right("0"+(DT_STR,4,1252)DatePart("yy",getdate()),2) + ".xls"
3. Use the Evaluate Expression button in the expression editor to see if the expression resolves the correct path to the file.
4. Modify the Expressions property of your FTP task and set the variable you created under LocalPath.

这篇关于SSIS - 用于FTP组件动态文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！