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.
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.
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.
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?
Thank you in advance!
EDIT:
Here is my updated code:
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;
}
}
}
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…