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.
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.
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.
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".
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?
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.