Assign value to variable using LINQ in SSIS Script task

Question

I am trying to loop through a folder. The suffix of each file can be found in a column in an SQL table. This suffix is the WHERE for my LINQ statement and is a variable in the script task (shown on comments). Where the predicate is true, I want it to return the value from another column on the same row in the SQL table, and assign the other column's value to another variable in my script task. For now I am just doing a MessageBox.Show to test.

I have set up an OLEDB connection manager in my SSIS package, I'm not sure how I can run the LINQ query inside the task, I think I'm missing something in how I have set up the connection. Any pointers would be gratefully received. Here's how far I've got:

public void Main()
{
    string sourcePath = Dts.Variables["User::sourcePath"].Value.ToString();
    string archivePath = Dts.Variables["User::archivePath"].Value.ToString();
    string destinationPath = Dts.Variables["User::archivePath"].Value.ToString();

    SqlConnection conn = new SqlConnection();
    conn = (SqlConnection)(Dts.Connections["Server.Database"].AcquireConnection(Dts.Transaction) as SqlConnection);

    DirectoryInfo directoryInfo = new DirectoryInfo(sourcePath);
    FileInfo[] files = directoryInfo.GetFiles();

    foreach (FileInfo fileInfo in files)
    {
        string fileName = fileInfo.ToString();
        fileName = fileName.Split('_')[0]; // File Suffix
        string destinationPrefix = "";
        // LINQ HERE USING fileName variable:  
        //destinationPrefix = FROM t in Table where t.Column == fileName select destinationPrefixColumn
        MessageBox.Show(destinationPrefix);            
    }
}

Marc Lyon Marc Lyon · Accepted Answer · 2016-09-07T14:22:22

Imran,

You need to give us more information on the Table object. There is no code to grab the data from the data base into the Table variable.

var query = "Select * from SomeTable Where SomeCondition Order by Somefields";
var Table = conn.ExecuteQuery(query);

That will get you a table which you can scan inside the fileinfo loop. Since you didn't tell us what fields to look at for the extension or what you are doing with the source and destination folders. I can give no further code. Also, have a look at some things in the fileinfo object which may be useful...

fileInfo.FileName //Name of file
fileInfo.FullName  // full path and name of file
fileInfo.Extension  // just the extension
fileInfo.MoveTo()  // will move file to new location
fileInfo.CopyTo()  // will copy file to new location

Post back and clarify your question to receive more help.

Assign value to variable using LINQ in SSIS Script task

1 Answers