如何在SSIS变量中存储“完全限定"和“仅名称"文件名 [英] How to store 'fully qualified' and 'name only' file name in SSIS variable

问题描述

我有一个SSIS包，其中有一个Foreach循环容器，可将所有.txt文件加载到静态文件夹中.我正在将完全限定的文件名作为在连接字符串中使用的变量传递.

I have an SSIS package that has a Foreach Loop container loading all .txt files in a static folder. I'm passing the fully qualified file name as a variable which I'm using in the connection string.

我现在只需要将文件名传递给变量以执行存储过程，麻烦的是，如果我将Foreach Loop集合更改为仅检索文件名，则会使其余文件无法正常工作.

I now need to pass just the filename to a variable to use to execute a stored procedure, trouble is if I change the Foreach Loop collection to just retrieve the filename it prevents the rest from working.

是否可以将完全限定和仅命名的文件名传递给ssis中的变量?

Is there a way to pass both the fully qualified and name only file name to a variable in ssis?

推荐答案

不，您可以选择一个:完全限定名称或仅文件名.就个人而言，如果除存储过程之外的所有内容都要求使用完全限定的名称，我将创建第二个变量FileNameOnly，并使用类似

Nope, you get to pick one: either fully qualified or file name only. Personally, if everything except the stored procedure expects fully qualified name, I'd create a second variable FileNameOnly and either populate it with an expression like

RIGHT(@[User::CurrentFileName], FINDSTRING(REVERSE(@[User::CurrentFileName]), "\\", 1 )-1)

(假设您已将值存储在名为CurrentFileName的变量中)

(this assumes you've stored the value in a variable called CurrentFileName)

或者您可以使用脚本任务将值分配给FileNameOnly并利用.NET

Or you can use a script Task to assign the value to FileNameOnly and leverage the .NET System.IO.Path.GetFileName method.

这篇关于如何在SSIS变量中存储“完全限定"和“仅名称"文件名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！