x

decision making in SSIS Data Flow

I am reading data from an XML file, going through some transformations and then putting into an Oracle database.

I do not want to execute the whole process if the XML file does not contain data. My question is: How do I test if the XML file has data and branch all in the same workflow?
more ▼

asked Oct 04, 2010 at 02:54 PM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Hi Raj,

you can't do expression constraint's in a workflow, it is something that i would like to see though, for example if you have other datasources that you read from, but if the MAIN source, in this case the XML source doesn't have any rows, then the dataflow will not read from the other sources.

To do what you want you will have to read the xml file. you could just create a simple dataflow that has a rowcount transform and after that dataflow, you could put a ExpressionAndConstraint constraint between the data flows, that only runs the second data flow when there is a positive rowcount variable?
more ▼

answered Oct 04, 2010 at 05:15 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

(comments are locked)
10|1200 characters needed characters left
rowcount is a good option, incase you wish to implement a script task you may use the IO libraries to read the fle and check the rowcount...it tends to ofer a similar functionality but you may wish to customize the custom component to suit you needs further and route the package flow accordingly.
more ▼

answered Oct 06, 2010 at 04:50 AM

Raunak Jhawar gravatar image

Raunak Jhawar
1

How do you use IO library to count from an XML file?
Oct 06, 2010 at 05:19 AM Raj More

Step 1: for every line read in the file increment a variable say count by 1

Step 2: if count>1 then process the package ahead else take the alternate
Oct 06, 2010 at 05:22 AM Raunak Jhawar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939
x587
x378

asked: Oct 04, 2010 at 02:54 PM

Seen: 2331 times

Last Updated: Oct 04, 2010 at 02:54 PM