SSIS Foreach Loop with Conditional Data Flows/Flat File Connections?

Robert Ong 1

I'm setting up a process to bulk insert daily flat files into a staging warehouse. The flat files are not very clean. The files for the first 40 days contains 20 column and the days after contain 22 columns.

How can I set up my foreach loop to switch between different data flows depending on what version of these files it's on?

So Ideally I'd like to set it up so that the loop will run flow A until it hits a certain date where it will switch over to use flow B.

Yitzhak Khabinsky 25,726 Reputation points

2020-08-27T14:58:08.06+00:00

Is it possible to distinguish two file types (20 vs. 22) based on file naming convention?
Yitzhak Khabinsky 25,726 Reputation points

2020-08-27T15:32:49.09+00:00

There is a custom component that might work for your SSIS version:
getting-file-properties-using-sql-server-integration-services-addin
Robert Ong 1 Reputation point

2020-08-27T15:43:20.29+00:00

Unfortunately no. The file types are supposed to be the same dataset so the file names the same with the addition of a date stamp at the end (datafile_20200827.csv).

But the provider made a change sometime along the way and added more columns. This breaks my flat file connector as it's expecting a line break delimiter after the 20th column but it's getting a column delimeter instead.
Yitzhak Khabinsky 25,726 Reputation points

2020-08-27T15:48:17.31+00:00

I hear you. It is much better to use data feed files in XML format instead of .csv files.
And 22 - 20 = 2 elements could be specified as optional.
Additional benefit that XML files could be validated by an XSD schema.
Robert Ong 1 Reputation point

2020-08-27T15:54:25.823+00:00

Sadly I don't think that's a possibility. That's why I was wondering if there's a solution to switch from one flow to another in the loop.

IIf date = X, run Data Flow A, else run Data Flow B. something like that.
Robert Ong 1 Reputation point

2020-08-27T15:59:36.057+00:00

After looking at the addin you linked, I think it may have provided a solution without using the addin.

I might be able to set up a precedence constraint and create a true false expression for each scenario. Only run flow A if date from file is less than X. Only run flow B if date from file is greater or equal to X.
Yitzhak Khabinsky 25,726 Reputation points

2020-08-27T16:12:58.627+00:00

You are right on target.
Robert Ong 1 Reputation point

2020-08-27T16:21:29.82+00:00

Just tested and it looks like it should work. I did get an error because it bounced back to flow A. Probably reading the date as a string so I'll need to figure out how to convert the variable to a date in the expression.

Thanks for the help!
Yitzhak Khabinsky 25,726 Reputation points

2020-08-27T16:26:50.173+00:00

Via casting (DT_DBDATE)@VAR
integration-services-data-types

2 answers

Stratos Matzouranis 36 Reputation points

2020-08-27T15:16:11.707+00:00

If you use T-SQL statement task with the use of ROW_NUMBER ?
Please sign in to rate this answer.
Robert Ong 1 Reputation point

2020-08-27T15:47:20.783+00:00

I'm not sure I follow this suggesting?

The problem is the delimeters in my flat file connection changes at one point because there are new columns so it goes from a LF delimeter to a comma delimiter. So I need a way to use connector A with a LF delimeter after column 20 for csv files up to date X and connector B with a comma delimeter after column 20 and a LF delimeter after column 22 after date X.
Sign in to comment
Monalv-MSFT 5,896 Reputation points

2020-08-28T03:24:47.317+00:00

Hi Robert Ong,

May I know if you have anything update by using Precedence Constraints?

Hope the following links will be helpful to you:

Using Precedence Constraints In SSIS

Overview of SSIS Precedence Constraints

Best Regards,
Mona

----------

If the response is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

SSIS Foreach Loop with Conditional Data Flows/Flat File Connections?

2 answers