SQLTeam.com | Weblogs | Forums

SSIS Help


#1

Hi All,

Here is my question. I want to know how it is possible below question in SSIS

Question: - Simple SSIS Package,
1 Step:- Execute SQL Task (Truncate Staging Table)
2 Step: - Data Flow Task (Reading data from Excel and Insert into SQL Table)
3 Step: - Using SQL Query as a source to create data from staging table (Populate in Step 2) and destination is mine.CSV File.

My question is, how can I stop create .csv file if Package fail in 3rd step?

I tried to use Sequence container, but it is still creating .csv if 3rd step error out.
Sequence Container ==> Transaction ==> Transaction Option is REQUIRED
Step 2 Data Flow ==> Transaction ==> Transaction Option is REQUIRED
Step 3 Data Flow ==> Transaction ==> Transaction Option is REQUIRED

Note: - The workaround would be event handler if Step 3 failed execute "Delete file", But I want to know other solution if there is any.

any advice?

Thank You.


#2

I don't get it. Step 3 is the step that created the csv file. Do you mean, "If step 3 fails (so the csv file is not created)" don't create the csv file?

That doesn't make sense to me.

However, FWIW, I would use a dataflow to create the csv file: OLEDB source from the table created in step 2, Flat File destination to the csv file you want.


#3

Sorry for confusion. In Step 3 I am creating a CSV file. If Step 3 runs successfully then generate CSV file. However, If Step 3 fails then it will not create CSV FILE. The reason why I am doing this is. i.e Source file has 200 rows. CSV file generated and insert 100 rows successfully, at 101 rows for some reason package fail. Now I have source file with 100 rows instead of 200.

Make sense?

Thank You.


#4

Yes, that makes sense. so, what I'd do:

Add a file system task to your control flow to delete the csv file. Connect step 3 to the new task and add a condition "Failure".


#5

Yeh. That I was thinking.
I would like to know, what is the purpose for "Sequence Container" It will execute if all task pass, right?


#6

that depends upon the connectors. if it is not connected to anything, it will run regardless, and in parallel with other tasks. if connected as a downstream component, it depends on how it is connected, "Completion" (unconditionally) "Success" (the default), "Failure" (if preceding task fails) and whether or not there are expression constraints.

usually I use a sequence container to logically separate parts of a package.