If CSV column contains x, import y - SSIS

[guest2013-1]

Hey guys, been delving into SSIS for a bit now, and there's an interesting problem I've come across.

I have a folder that CSV's get dumped in. 4 files daily.

1 Header that contains a summary of what is exported, 1 Detail file (that the header explained)

4 files in total, the first 2 (header/detail) contains a summary of the day (contains total time/who did it etc) and the other 2 files (header/detail) contains the detail of that day (a break down of what makes the "total time" in the other file)

These 4 files get dumped in the same directory, and the naming convention isn't distinctive enough to say "hey you, you go there!"

What I do have is the header files "explaining" which file contains what.

What I want to do now is have my SSIS project import these files based on what 1 column contains or not. Because in the description of the file, it says it Ignores Sub-Points (which the other one doesn't ignore, creating the whole Summary/Detail thing) and then send them to the appropriate tables.

The problem is, the previous programmer did a simple "skip 1 if processing the first 1" type approach which is dumping to the incorrect tables in SQL if there's more than 4 files. I've requested they try to dump each in a separate folder for me so I can know definitively that THIS one GOES THERE

But in the meantime (while I wait for business to make up their mind) I'd like to know if what I want to do is possible and HOW

 

[SBSP]

I dont know if i understand what you are saying.

1.Header line
2.Detail Line
3.Header Line
4.Detail line

So when you import the header you import Line 1 Skip 1 import line 3 and the same with the detail ?
But now you want to import based on a field in the line ?


Why can you just run the result to another temporary SQL table to split the header separately from the detail.
Then dump 2 files and import the header & detail separately.

 

[*SynergyX*]

if i understand your problem correctly -- you want to read your first record(column by column into separate variables using only the ones you need) into a variable and in the output(the green arrow - you can put a condition on this to say eg. record1var = "X") of that shape compare it to what you looking for -- if the condition is met only then will it continue on that path -- then proceed from there...

 

[acidman1]

In my experience it is easier to import everything into a temp db and then use TSQL to figure out what goes where. Then use SSIS to execute the procedures. Alternative is to load the file data into variables/datasets and then use expressions to read from it.

 

[*SynergyX*]

In my experience it is easier to import everything into a temp db and then use TSQL to figure out what goes where. Then use SSIS to execute the procedures. Alternative is to load the file data into variables/datasets and then use expressions to read from it.

true dat---

 

[guest2013-1]

the header and detail I referred to are *files*

why moer everything into a tempdb and use TSQL to decide where what goes if you have SSIS to handle all of that?

Basically what I did was:

Execute SQL task (to get the location/region of where the files are located) ->
For Each loop Container for ADO ->
Get a list of files to exclude for the region/location combination to avoid re-importing the same files since I don't have move/delete permissions on the directory ->
For Each Loop Container for Filesystem (that gets fed the location from the SQL Task earlier) ->
Script Task that opens up the header file, determines if it's a summary or detail and marks a user created variable ->
Precedence Constraint with an expression that evaluates true or false depending on this user created variable ->
Data Flow from Flat File Source, adding "Region" using a Derived Column going to an OLEdb Database ->
Log (so the successful file gets excluded when it loops again later)

Now I have an SSIS package that can import the same file type for multiple locations (network/hard drive) for multiple regions (which is where the multiple locations come in), not import the same file over and over and keeps a log of what happened where and what.

All without having to dump files to a tempdb first and then reworking it with tsql.... like SSIS intended

I must admit DEBUGGING IS A BITCH with SSIS. All I want to have is a break-point and F11 and a view where I can see what data is set, where and to what and what the result is. But noooooooooooooooooooooo