What's the datatype of the column in the target table?

John's solution handles everything up to 999, not just 100.

We don't know your tables, your data, your business...

OK, I think I worked it out. Do you mean something like this? By the way, please include sample DDL next time.

if object_id('tempdb..#Order', 'U') is not null

...

winston Smith (10/20/2014)

---

I have data in the following format

OrderID ItemName UnitPrice Quanity

1 ...

Then where were you hoping...

Do you have a table somewhere which includes both the code and the description?

May I ask why you are using a cursor for this?

I'm not sure how a script task would help you here.

I think you may mean a script...

coreyjbaum (10/14/2014)

---

I have a package that creates and excel file and dumps some data in, and then renames the worksheet to the current date using a script task at the...

Sure. Have a look here.

The CREATE TABLE will need to be a dynamic Execute SQL task which happens before the data flow (obviously).

rocky_498 (10/13/2014)

---

I am using SQL Table to get the file name. First step Insert all FolderPath+FileName to SQL Table and then using the file name Variable in Foreach Loop Container...

Sounds like you need to set the Excel sheet name dynamically via an SSIS Expression. Or are you doing that already?

Are the tab names in the spreadsheets all the same?

--Edit: ignore that – I just read your post more carefully

And thank you for posting back so positively 🙂