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 🙂

Based on your description, a script task is not what you need for this job.

Have a look here for an example of how to import a fixed width flat file...

Instead of SELECT @RetVal in my above example,

select left(@RetVal,len(@Retval)-1)

Returns everything apart from the rightmost character. This may be what you need ...

--Edit: fixed inconsistent formatting

Comment deleted. I answered the wrong question! +1 to Alvin's answer.

There's a post which may help you here.

Here is a solution which relies on Jeff Moden's infamous '8k Splitter'[/url].

It assumes that the DelimitedSplit8K function has been created in the database you are going to call it from.

if...

When you call this function, will you always pass exactly 2 arguments (eg 1 2), or do you want to pass a parameter list with a variable list of params?

Have you recently renamed your SQL Server instance?

cbrammer1219 (10/10/2014)

---

Ok...This may be a strange question< but after the first initial load would I need to changed this SSIS package so it wouldn't reload all of these tables again....

In...

Deleted comment