SSIS suitability

Question

I'm tring to create an SSIS package to import some dataset files, however given that I seem to be hitting a brick

wall everytime I achieve a small part of the task I need to take a step back and perform a sanity check on what I'm

trying to achieve, and if you good people can advise whether SSIS is the way to go about this then I would

appreciate it.

These are my questions from this morning :-

http://stackoverflow.com/questions/3203036/debugging-ssis-packages-debug-writeline

http://stackoverflow.com/questions/3202439/changing-an-ssis-dts-variables/3202884#3202884

What I'm trying to do is have a For..Each container enumerate over the files in a share on the SQL Server. For each

file it finds a script task runs to check various attributes of the filename, such as looking for a three letter

code, a date in CCYYMM, the name of the data contained therein, and optionally some comments. For example:-

ABC_201007_SalesData_[optional comment goes here].csv

I'm looking to parse the name using a regular expression and put the values of 'ABC', '201007', and

'SalesData' in variables.

I then want to move the file to an error folder if it doesn't meet certain criteria :-

• Three character code
• Six character date
• Dataset name (e.g. SalesData, in this example)
• CSV extension

I then want to lookup the Character code, the date (or part thereof), and the Dataset name against a lookup table

to mark off a 'checklist' of received files from each client.

Then, based on the entry in the checklist, I want to kick off another SSIS package.

So, for example I may have a table called 'Checklist' with these columns :-

Client code       Dataset          SSIS_Package
    ABC           SalesData        NorthSalesData.dtsx
    DEF           SalesData        SouthSalesData.dtsx

If anyone has a better way of achieving this I am interested in hearing about it.

Thanks in advance

Answer 1

Unfortunately, you have just missed the SQLLunch livemeeting on the ForEach loop: http://www.bidn.com/blogs/BradSchacht/ssis/812/sql-lunch-tomorrow

They are recording the session, however.

Answer 2

That's an interesting scenario, and should be relatively easy to handle.

First, your choice of the Foreach Loop is a good one. You'll be using the Foreach File Enumerator. You can restrict the files you iterate over to be just CSVs so that you don't have to "filter" for those later.

The Foreach File Enumerator puts the filename (full path or just file name) into a variable - let's call that "FileName". There's (at least) two ways you can parse that - expressions or a Script Task. Depends which one you're more comfortable with. Either way, you'll need to create three variables to hold the "parts" of the filename - I'll call them "FileCode", "FileDate", and "FileDataset".

To do this with expressions, you need to set the EvaluateAsExpression property on FileCode, FileDate, and FileDataset to true. Then in the expressions, you need to use FINDSTRING and SUBSTRING to carve up FileName as you see fit. Expressions don't have Regex capability.

To do this in a Script Task, pass the FileName variable in as a ReadOnly variable, and the other three as ReadWrite. You can use the Regex capabilities of .Net, or just manually use IndexOf and Substring to get what you need.