SSIS 脚本任务获取文件名并存储到 SSIS 对象变量 [英] SSIS Script Task Get File Names and Store to an SSIS Object Variable
问题描述
我正在尝试构建一个将在标准化文件系统归档过程中使用的 SSIS 包.基本上，我将能够将信息添加到配置表，然后使用该表将某些文件归档到指定文件夹中.我的问题是很多文件都有动态命名，所以我需要获取所有文件的列表，然后查询以决定我应该接触哪些文件.
I am trying to build an SSIS package that will be used in a standardized file system archive process. Basically, I will be able to add information to a configuration table and then use this table to archive certain files in specified folders. My issue is that a lot of the files have dynamic naming so I need to get a list of all files and then query to decide which files I should be touching.
我不是 C#/VB 程序员在尝试编写包的一部分脚本时会导致一些问题，即抓取指定网络目录中的所有文件，然后将这些文件名反馈给 SSIS 对象变量.
Not being a C#/VB programmer is causing me some problems when trying to script a portion of the package the grabs all files in a specified network directory and then feed these file names back into an SSIS object variable.
我有一个字符串变量User::SourceNetworkFolderName"，它将包含我想从中读取所有文件的文件夹的 UNC 位置.然后我想将所有这些文件名(带扩展名)传递回名为User::SourceFilesInTheDirectory"的 SSIS 对象变量.将文件名列表放入对象变量后，我打算将它们 foreach 循环到 SQL 表中.
I have a string variable 'User::SourceNetworkFolderName' which will contain the UNC location of the folder I want to read all files from. I want to then pass all these files names (with extension) back to an SSIS object variable called 'User::SourceFilesInTheDirectory'. Once I have the list of file names into the object variable, I was going to foreach loop them into an SQL table.
有人对我如何将所有文件名的列表从我的变量目录获取到我的 SSIS 对象变量有任何具体建议吗?
Does anyone have any specific suggestions on how I go about getting a list of all files names from my variable directory to my SSIS object variable?
先谢谢你！
这是我更新的代码:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace ST_f5e4ae71f14d40d8811af21fa2a9a622.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
//Setup Connection String to SQL
SqlConnection SQLConnection = new SqlConnection(
//"user id=username;" + //UserName
//"password=password;" + //Password
"Trusted_Connection=true;" + //Windows Auth
"server=SERVERNAME;" + //SQL Server
"database=DATABASENAME; " + //SQL Database
"connection timeout=30;" + //connection timeout
"Network Library=dbmssocn"); //TCP/IP Connection ("dbnmpntw" = Name Pipes)
//Open the SQL Connection and remove the error code
try
{
SQLConnection.Open();
}
catch (Exception OpenConnectionError)
{
Console.WriteLine(OpenConnectionError.ToString());
}
//Fetch a list of files from 'SourceNetworkFolderName' SSIS variable to an array called array1.
string[] ArrayFileName = Directory.GetFiles(Dts.Variables["SourceNetworkFolderName"].Value.ToString());
//Set up sql variable for table population
SqlParameter SQLFileNameParam = new SqlParameter("@FileName", SqlDbType.VarChar, 100);
//Loop through the array and insert into an SQL table
foreach (string strFileName in ArrayFileName)
{
//Update sql variable with file names from array
SQLFileNameParam.Value = strFileName;
//Make the table insert
SqlCommand SQLInsertToTable = new SqlCommand("INSERT INTO Archive_Extract_Network_Folder_File_List (FileName) VALUES (@FileName)", SQLConnection);
//This snippit allows the use of the variable in the sql script.
SQLInsertToTable.Parameters.Add(SQLFileNameParam);
//Execute SqlCommand
SQLInsertToTable.ExecuteNonQuery();
//Clear the parameters and set the object to null
SQLInsertToTable.Parameters.Clear();
SQLInsertToTable = null;
}
//Close the SQL Connection and remove the error code
try
{
SQLConnection.Close();
}
catch (Exception CloseConnectionError)
{
Console.WriteLine(CloseConnectionError.ToString());
}
//Set array to null since it is no longer required.
ArrayFileName = null;
//Exit on success
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
推荐答案
在您的脚本中，只需构建一个文件名数组并将该数组设置为您的变量(确保该变量在脚本任务中设置为可写).如果变量是 object 类型，则可以在后续任务中使用 for 循环对其进行迭代，并对文件执行任何操作.您不应该仅仅在一个脚本中创造任何奇迹.
Inside your script, just build an array of the file names and set that array to your variable (make sure the variable is set to writable in the scripting task). If the variable is of type object you can then iterate on it using a for loop in a subsequent task and do whatever you want to with the files. You should not need to work on any miracles solely in one script.
将源目录下的所有文件放在一个数组中:
Put all files under your source dir in an Array:
string[] array1 = Directory.GetFiles(Dts.Variables("SourceNetworkFolderName").Value.ToString());
将所有扩展名为BIN"的文件放在一个数组中:
Put all files with an extension of "BIN" in an array:
string[] array2 = Directory.GetFiles(Dts.Variables("SourceNetworkFolderName").Value.ToString(), "*.BIN");
您可能需要在脚本代码的顶部包含 System.IO.
You may need to include System.IO at the top of your scripting code.
编辑:
将数组转换为列表以供循环任务处理.调用上面的代码后，调用这个:
To convert array to a List for processing by the Loop task. After calling the code above, call this:
List<string> fileList = new List<string>(astrTest);
Dts.Variables["SourceFilesInTheDirectory"].Value = fileList;
您需要在脚本文件的顶部包含 System.Collections.Generic.
You will need to include System.Collections.Generic at the top of your script file.
这篇关于SSIS 脚本任务获取文件名并存储到 SSIS 对象变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
