Or, if you can be bothered setting things up so that you have access to the Excel object model via script (a bit of a pain), you can get the...

roundel1900 (8/11/2009)

---

I don't...

Do I have to do everything? I said it was untested and expected that you could work it out ...

Turns out there is no LEFT function available, so you'll have...

Ah - you said you were having a problem stripping off leading zeros and that confused me, but now I understand.

So you need something like (completely untested)

LEFT('00000', 5 - LEN(Zip))...

OK, I think an easy way to do this is to put in a data conversion transformation and convert from your current string field to an integer - this will...

If you really appreciate problems, I have some really juicy ones for you :laugh:

Can you provide sample data - source and required output - please? I'm English & not really...

Can you throw away the old record, if a newer one exists, and just use the newer one for everything?

OK, just tried this with my own system:

"select f1, f2 from account where accountid = '" + @[User::AccountID] + "'"

is the value of SqlStatementSource, under Expressions.

Did you try using the 'Expressions' property as I suggested? Otherwise your statement will not get evaluated ...

You will be able to paste your variable into your SQL statement using...

roundel1900 (8/11/2009)

---

Maybe I am not explaining this correctly. Yes it appends the rows but without regards to the keys. So as it stands now I can continually append the exact...

Did not! 🙂 'This' is a hyperlink in my post. Happy hunting.

It is likely that you can use an Expression to set the SQLStatementSource property to get what you want, without the need for all the faffing around you mention in...

Not easy. You'll probably need to get hold of the Excel object model and manipulate the spreadsheet via a Script task before and after the updates to get anywhere with...

Appending rows is the default behaviour. Any other requirement (UPDATE or replace, for example) would require additional steps.

Unless you are prepared to take the time to provide a few more...

All you need to do is update the meta data for the source connection to include the extra columns - but you don't need to send them through the pipeline....