As you did not provide detailed DDL, INSERT and desired results, I've had to guess. Does this help?

DROP TABLE IF EXISTS #t1;

CREATE TABLE #t1
(
   ...

Change the package / config so that the connection uses the credentials you want it to use.

Are you certain that the SSIS package is executing in the context of the Windows user you are expecting?

Please check the All Executions report to verify this (look for the...

Try running this on your sample data ... it performs the split for you:

SELECT m.ID
      ,REPLACE(REPLACE(REPLACE(ss.value, '"', ''), '[', ''), ']', '')
FROM...

For info, here is the original code, formatted and pasted directly from VS:

IF
(
    SELECT FormType = LTRIM(RTRIM(ISNULL(f1, '') + ISNULL(f2, '') + ISNULL(f3, '')))
...

A Script Component source would be the way to go, in my opinion, not a Script Task.

It needs to read the file (just one row, based on your example) and...

As Thom suggested, having | as both row and column delimiter is going to confuse the hell out of SSIS and I'd not expect you to have any success doing...

So now you see...

That's more than four columns. What is your row delimiter?

--Edit: I seem to have regurgitated Thom's first few words almost verbatim, apologies! But these are critical questions

• This reply was modified 6 years, 7 months ago by Phil Parkin.

You mentioned an UPDATE query, yet table 3 contains no data (and therefore no amount of UPDATE queries will help). Are you sure that your test data is up to...

What have you tried so far?

I think not. You should really test things before posting them here. Using a precision of 0 causes immediate rounding to the nearest whole number.

This example seems over-simplified, to the point of being impractical.

To implement this in the real world, I'd be looking to develop a standard BOM model ... see here, for...

Is this a blog post, or do you have a question?

I took this from https://regex101.com/:

The point being that the string which you are extracting does not even match the expression from which it was extracted. How...