SSIS: get any flat file source from a folder and cache the name as a super global variable

Question

SSIS: get any flat file source from a folder and cache the name as a super global variable

I work in SSIS and Visual Studio 2008. At runtime, I need the SSIS package to perform the following tasks:

Check folder for file
If the file exists, take the file and use it as the source for the flat file
Save the file name in a global variable that can be accessed in other parts of my package.

The package will be launched by another script. Thus, we need it to check the file every time the package is launched. We are trying to prevent a scenario where we must monitor the folder and execute the package manually when the file appears.

Any suggestions?

+3

ssis

Snow_mac Jun 03 '11 at 15:59

source share

2 answers

. , Foreach Loop. , . , .

:

SSIS 3 , # 1. CheckFile . Folder , . Filename . FilePath , . , , .
Control Flow Flow Foreach Loop container a Script Task. Script , , Foreach Loop. . # 2.
ForEach Loop, # 3 # 4.
Main() S cript Task , Script task code. , FilePath.
# 5 , c:\temp\ , # 6 .
# 7 , TestFile.txt c:\temp\, # 8 .
, , Data Flow Task Foreach Loop container .

, .

Script :

#, SSIS 2008 and above..

public void Main()
        {
            Variables varCollection = null;

            Dts.VariableDispenser.LockForRead("User::FilePath");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            if (String.IsNullOrEmpty(varCollection["User::FilePath"].Value.ToString()))
            {
                MessageBox.Show("File doesn't exist.");
            }
            else
            {
                MessageBox.Show("File " + varCollection["User::FilePath"].Value.ToString() + " exists.");
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

№1:

№2:

№3: 

№4:

№ 5:

№ 6:

№ 7:

# 8:

+5

user756519 03 . '11 16:24

billinkc · Accepted Answer · 2011-06-03T16:33:29+0000

- Foreach Loop, "" (, ). , 2 , FileName ( ) InputFolder, "where",

ForEach Loop Editor

Collection tab:  
Enumerator = Foreach File Enumerators
Expression: Directory = @[User:InputFolder]
FileSpec: "YD.*"

Retrieve file name
* Fully qualified

Variable Mappings tab:  
Variable: User::FileName 
Index:  0

Collection tab Variable Mappings Tab

script, , .

script , InputFolder FileName. script InputFolder , FileName /.

using System;
using System.Data;
using System.IO;  // this needs to be added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

// namespace will vary 
namespace ST_bc177fa7cb7d4faca15531cb700b7f11.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()
        {
            string inputFolder;
            string fileName;
            inputFolder = Dts.Variables["InputFolder"].Value.ToString();

            // File, if exists will look like YD.CCYYMMDD.hhmmss.done
            string fileMask = "YD.*.done";

            // this array will catch all the files matching a given pattern
            string[] foundFiles = null;
            foundFiles = System.IO.Directory.GetFiles(inputFolder, fileMask);

            // Since there should be only one file, we will grab the zeroeth
            // element, should it exist
            if (foundFiles.Length > 0)
            {
                fileName = foundFiles[0];

                // write the value to our global SSIS variable
                Dts.Variables["FileName"].Value = fileName;
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

SSIS: get any flat file source from a folder and cache the name as a super global variable

More articles: