SSIS & MS SQL Server: Parse Text From A File Name

Question

Quick intro to the existing process: an SSIS foreach loop imports a flat files into a SQL Server table by looking for any text file in the entry path folder, after the import, it dumps the flat files into an archive folder. This happens with a total of four flat files and since it's only looking for any text file, it has no problem.

However, the new idea presented was instead, SSIS should run a process which first captures the date and State off of the text file (from the file name, not inside the file), then insert the data into SQL Server along with the State and date, which was captured, then archive the files. The idea behind this was that if a date was a month behind or ahead, SSIS wouldn't import the file (or if the year was wrong, it wouldn't import it).

So now - SSIS first needs to check to see if four files exist. - If four files exist, then it moves into the foreach loop (at least, I'm guessing this would be the best way to approach this problem). - In each loop, a process needs to parse the date and State from the file name and it needs to import the data into SQL Server, adding the date and State parsed into the appropriate columns. - Then it needs to archive that file and move on to the next one.

I've never done any parsing, and I tried playing around with expressions and connection strings to search for the current month and year, but no luck.

(Finally, I know very little C#, so script tasks are out of my league).

Answer 1

I don't see this happening without a script component. If it does, the solution is going to be a bunch of messy expressions.

You can do a script component in vb.net or c#. Get familiar with the system.IO namespace. The Directory.GetFiles() method will return all files in a given directory. You can do a For Each loop and parse each filename.

If you are serious about SSIS, you need to get familiar with the script component (including reading from and writing to variables), the system.io namespace, and how to step through the component code to explore properties. I rarely create a package that doesn't involve programatically accessing the filesystem, whether it be to parse a filename, read a file, move a file, or delete a file. This can all be done with sys.io.

Answer 2

SSIS Expressions are probably not the right tool for this job.

I'd be thinking of a script task - I've stuck with VB which I find more readable and achieves the same result. This unlocks the beautiful Try .. Catch block and incredibly rich .NET libraries e.g. Regular Expressions. You will never look back.

Answer 3

Forgot to post my solution: without using any C# or VB, I found a way to insert the name of the file into a table and parse the file name using TSQL. Using an "Execute SQL Task" you can use the SQL statement below this:

INSERT INTO Table VALUES (?)

With a string variable for the project set to be the current file's name, you will set that under "Parameter Mapping." From there, you'll have the full file name in a SQL table and can parse it.