That was fun! 🙂

Use this as your derived column definition:

SUBSTRING([OpenDate],FINDSTRING([OpenDate],"/",2) + 1,4) + "-" + SUBSTRING([OpenDate],1,FINDSTRING([OpenDate],"/",1) - 1) + "-" + SUBSTRING([OpenDate],FINDSTRING([OpenDate],"/",1) + 1,FINDSTRING([OpenDate],"/",2) - FINDSTRING([OpenDate],"/",1) - 1) + SUBSTRING([OpenDate],LEN([OpenDate])...

It does feel like it is permissions related, but ...

..if it were, why would the Script Task work and the File System Task have problems?

I've also had problems doing this sort of thing in the past and ended up scripting it ... I know others have too (I've seen the forum posts) - this...

I think Lynn is correct: export the package out into the file system and then open it in BIDS to see what the steps are doing ...

By the way, I...

Yes

Is there a delimiter (eg "") around company name? Or is it just

Field1, co, name, here, field2

Field1, another company name here, field3

?

If there is no field delimiter, you are...

Provide some sample data please.

You could have a look here and see where the links take you.

Correct,

Some sample data (source & desired output) would illuminate this for others who are reading ...

You are probably going to end up using the Script Component within a dataflow to...

Use a SQL Server statement as you suggest (you can get the result of a single-valued T-SQL query into a variable (say MaxID) directly from the Execute SQL task).

Then use...

Please consider posting this in a more appropriate forum ... one related to Crystal, perhaps? 🙂

Sounds like an automated backup/restore process might be more efficient - are you effectively just replacing the dev db?

Are the files all of the same format? Are they going to the same destination folder?

Another alternative, of course, is to do it all in one hit at the end, using a simple UPDATE query.