SSIS For 循环停止工作 [英] SSIS For Loop Stopped working
问题描述
我的 ssis 包中有一个 for 循环容器,其中包含一个脚本和一个 sql 任务.
我有 3 个变量.
source.string = 这是文件夹位置file.string = 我使用了通配符 = *.csv存在.int =默认为0
我将 innitexpression 值设置为 @Exists=1并且将 evalexpression 值设置为 @Exists=1
在脚本中,我将其设置为查看源变量,如果存在 file.string 变量,则将存在变量设置为 1
问题是它只是循环它应该只在没有文件的情况下循环.在我将变量更改为通配符 *.csv
之前,我看不出我是如何做错的我已经使用另一个包含文件名而不是通配符的变量对其进行了测试,并且它工作正常,问题是在寻找文件名的通配符后跟扩展名时.为什么是这样?我可以不通过通配符变量吗?
我的脚本任务是
public void Main(){//TODO: 在此处添加您的代码string Filepath = Dts.Variables["User::Source"].Value.ToString()+ Dts.Variables["User::file"].Value.ToString();如果 (File.Exists(文件路径)){Dts.Variables["User::Exists"].Value = 1;}///MessageBox.Show (文件路径);///MessageBox.Show(Dts.Variables["Exists"].Value.ToString());Dts.TaskResult = (int)ScriptResults.Success;}#region ScriptResults 声明///<总结>///这个枚举在这个类的范围内提供了一个方便的速记,用于设置///脚本的结果.//////此代码是自动生成的.///</总结>枚举脚本结果{成功 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,失败 = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure};#endregion}}
根据上面的评论,我提出了 2 个不同的解决方案.您现在的解决方案是否定的.2
<块引用>这个可以根据路径中的多个文件搜索特定文件.它需要一些调整,但如果你想用通配符检查特定文件是否存在可以使用
如果找到任何通配符文件,则此结果为真.
C# 代码 1
使用 System.IO:string Filepath = Dts.Variables[User::Source"].Value.ToString();string WildCard = Dts.Variables["User::file"].Value.ToString();//以文本形式@*.txt";字符串完整路径 = 文件路径 + 通配符;//带for循环字符串 txtFile = null;//获取所有带通配符的文件string[] allfiles = Directory.GetFiles(Filepath, WildCard);//遍历所有文件并在txtFile中设置文件名.在这里为所欲为foreach(所有文件中的字符串文件名){//检查文件是否包含某些内容,它可能是您只想要的前缀名称if(fileName.Contains(txt")){txtFile = 文件名;如果(文件.存在(txtFile)){Dts.Variables[User::Exists"].Value = 1;}}}
C# 代码 2
使用 System.IO;使用 System.Linq;string Filepath = Dts.Variables[User::Source"].Value.ToString();string WildCard = Dts.Variables["User::file"].Value.ToString();//以文本形式*.txt";字符串完整路径 = 文件路径 + 通配符;//带布尔值bool 存在 = Directory.EnumerateFiles(Filepath, WildCard).Any();如果(存在 == 真){Dts.Variables[User::Exists"].Value = 1;}MessageBox.Show (文件路径);MessageBox.Show(Dts.Variables[Exists"].Value.ToString());
I have a for loop container within my ssis package which contains a script and a sql task.
I have 3 variables.
source.string = this is folder location
file.string = i have used wildcard = *.csv
exist.int = defaulted to 0
I have the innitexpression value set to @Exists=1 and the evalexpression value set to @Exists=1
in the script I have set it to look at source variable and if file.string variable exists then set exist variable to 1
problem is it just loops it should only loop if no file there. cant see how I've done this wrong it was working before I changed the variable to be a wildcard *.csv
I have tested it using another variable which contains a filename rather than a wildcard and it works correctly the issue is when looking for a wildcard for the filename followed by the extension. why is this? can I not pass through a wildcard variable?
my script task is
public void Main()
{
// TODO: Add your code here
string Filepath = Dts.Variables["User::Source"].Value.ToString()
+ Dts.Variables["User::file"].Value.ToString();
if (
File.Exists(Filepath))
{
Dts.Variables["User::Exists"].Value = 1;
}
/// MessageBox.Show (Filepath);
/// MessageBox.Show(Dts.Variables["Exists"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
Based on comments above i made 2 different solutions. The solution for you right now would be no. 2
This one can search for a specific file based on multiple files in your path. It need some tweaking but can be used if you wanna check if a specific file exists with wildcard
This one evaluates to true if any wildcard file is found.
C# Code 1
Using System.IO:
string Filepath = Dts.Variables["User::Source"].Value.ToString();
string WildCard = Dts.Variables["User::file"].Value.ToString(); // In Text form @"*.txt";
string fullpath = Filepath + WildCard;
//With for loop
string txtFile = null;
// Gets all files with wildcard
string[] allfiles = Directory.GetFiles(Filepath, WildCard);
//Loop through all files and set the filename in txtFile. Do whatever you want here
foreach(string fileName in allfiles)
{
//Check if a file contains something, it could be a prefixed name you only want
if(fileName.Contains("txt"))
{
txtFile = fileName;
if(File.Exists(txtFile))
{
Dts.Variables["User::Exists"].Value = 1;
}
}
}
C# Code 2
Using System.IO;
Using System.Linq;
string Filepath = Dts.Variables["User::Source"].Value.ToString();
string WildCard = Dts.Variables["User::file"].Value.ToString(); //In text form "*.txt";
string fullpath = Filepath + WildCard;
//With bool
bool exists = Directory.EnumerateFiles(Filepath, WildCard).Any();
if(exists == true)
{
Dts.Variables["User::Exists"].Value = 1;
}
MessageBox.Show (Filepath);
MessageBox.Show(Dts.Variables["Exists"].Value.ToString());
这篇关于SSIS For 循环停止工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!