How to check the number of columns in a source dynamically?

Question

How to check the number of columns in a source dynamically?

sultan_vnj

SSC Veteran

Points: 229
More actions
June 13, 2011 at 12:36 am

#252671

Hi There,
in my package when pulling the data from the source
today i have 4 columns, but some other day the number of columns may change.
So, how to check the number of columns in the source and send them to transformation output(error or redirecting to other destination).
Thanks,
Sultan.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

You should be able to use the Data Profiling task to build a profile of your data into a table, then use that data in to make decisions in your data flow. This is a fairly complicated process and beyond the scope of a forum answer. Spend some time on Google learning about the task and how to take the data into a database. Good luck.

Nimesh_Parikh SSC-Addicted Points: 464 More actions · Answer 2

Well, If your columns get change then you have to update you component/DFT manually.

Use DMVs of SQLServer for finding all system level information, you will get all information from that.

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 3

This is not going to be an easy task, you could however have a process that runs daily and compares the source and target system and then emails the comparision results to you.

But you would still need to go and change the Data flow transformations manually to add or modify any column mapping after the table structures have been modified.

Another way of avoiding this change is to make use of BCP command although you would still need to go and make changes to your table structure.

Use view as a source and keep adding or deleting columns in the view depending on the changes in the source, that way atleast you don't have to touch your main processing packages.

Just some thoughts out here.

Amol Naik

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

sultan_vnj (6/13/2011)
Hi There,
in my package when pulling the data from the source
today i have 4 columns, but some other day the number of columns may change.
So, how to check the number of columns in the source and send them to transformation output(error or redirecting to other destination).
Thanks,
Sultan.

To determine the number of columns in the "source", input the first row as a single element, count the number of delimiters, and add 1. That's how many columns there are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dg227 SSCrazy Points: 2017 More actions · Answer 5

One other thought: if you have the same number of base fields that you need to work off of every day (say, the first four columns), and everything after those first four can safely be discarded or sent to some other capture, you could do it.

What you'd need to do is read in the data record as one large, non-delimited text field. As the number of fields could vary, you would first want to ensure that the last character in the record is whatever delimiter you work off of, so you'd first need to tack that delimiter on to the end of the text record in a Derived Column component. Next in the Data Flow you can then shred that text record in another Derived Column component, based on that constant, known field delimiter, into your required columns (4, in this case). Everything in that inputted text record after the fourth delimiter, in this example, can then be discarded/captured separately, if needed.

If the number of fields that you actually need to use in your data flow changes (3 fields today, 5 tomorrow), then you probably would not be able to use the above technique.