I just tried it and it worked as I suggested.

Against the properties for your flat file source, have you got 'retain nulls' set to true?

Something else is happening here.

If I import the record

Affinity,,CT

into a database, I can guarantee that field number 2 would not somehow become a space in my database table. Do you...

Sounds like a good solution to me.

dndaughtery (2/4/2010)

---

Yeah its in a script transform in a data flow component. I basically need a way to add a unique record id for each record. I'm open to any...

You use the word 'retain' in your original post. If a value is null in a database column and you export it to a text file, the way of retaining...

,, in a text file is the equivalent of NULL in a database table.

PATINDEX is not a valid function in SSIS expressions.

Maybe adding an additional condition would help:

&& FINDSTRING(Email,"@",1) < (LEN(Email)) - FINDSTRING(REVERSE(Email),".",1))

Wouldn't this fail at the import stage, because SSIS would validate the number of fields...

Please show an actual example of the data in your flat file which shows how 'blank' and 'null' differ.

Can you use a T-SQL query for your source data? COALESCE would do the work for you very nicely.

I do not have the experience to argue either way - just thought I'd raise it as a point to be considered.

You would not use the Script Task to do...

wschampheleer (2/1/2010)

---

To find the min/max value(s) you can use a Multicast transform and feed one flow to an Aggregate Task.

Write the output to a temporary file (eg RAW).

Add a second...

Do you work for this company?

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?