I appreciate the response, but unfortunately, this didn't work.  The first file, with the "dummy" data loaded great.  I then changed the setting on the Excel Source step to...

It's been a while since I last worked on this issue, so I may have misremembered the 'solution'.

After your package has run against the dummy Excel file with the wide...

I haven't fully tested this, but it should get you started:

DROP TABLE IF EXISTS #SomeData;

CREATE TABLE #SomeData
(
    entity VARCHAR(50)
   ,col VARCHAR(50)
 ...

• This reply was modified 4 years, 4 months ago by Phil Parkin. Reason: Removed unnecessary columns from select

For those who enjoy the additional challenge of working on the same question in multiple parallel threads, this question is also posed here.

To the OP: why did you cross-post? You have enough points to know better. Do you realise that this annoys those people who take the time to answer in one...

You'll need to create two AD groups, one for standard users, who should not see the sensitive columns, and one for privileged users, who can.

Drop the 'all logins' group...

Your BEGIN ... END surround a single statement and should therefore not be required, unless you put them there for clarity.

Nearly! More like this, I think:

INSERT a2wh.dbo.CallLogCommon_dbc
(
    CALL_ID
   ,DATE
   ,TIME
   ,lead_id
   ,DOMAIN
   ,SESSION_ID
 ...

Yes indeed, that should work.

OK, I can't think of any reasonable way of doing that! I'll leave it for others to chip in.

They say that they still need to use the domain logon as everyone needs access but want to secure the sensitive information on certain tables but still be accessible...

I've never tried this, but if you use the (oxymoronically named) ACE driver to read the text file, using a connection string like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<filepath here>;Extended Properties="TEXT;HDR=YES;IMEX=1";

after...

Some background information from you would help illuminate the problem, I think.

So far, it seems that you periodically receive one-off files which contains hundreds of columns, which you would like...

As you are familiar with SSIS, you will probably know that you can redirect errors to an error table.

If your target staging table is set up with all of the...

What action do you wish to take if validation fails (eg, invalid date) for a particular row?