SSIS multiple Destination tables

i have to import multiple text files with same schemea format
into different destination tables..
bUT I think SSIS Cannot create destination tables on the fly..if this is the case,
 can we not load the data into a single
table intially and then split them into different tables based on the filenames
with the help of some stored procudure or .net code ?
can anybody please help me.. Many Thanks
gvamsimbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PedroCGDCommented:
You can try store the tablenames you want to create in a table and use a stored procedure to create the tables... try it!
Pedro
www.pedrocgd.blogspot.com
0
gvamsimbaAuthor Commented:
thanks pedro, can u please give me that dynamic stored procedure
for just 2 tables say file1 and file2..and if possible can u also
let me know if can add that SP in the SSIS package ?
many thanks..
0
PedroCGDCommented:
CREATE PROCEDURE usp_createtable_dinamic
      @myTable as varchar(30)
AS
DECLARE @SQL nvarchar(max)
 
    SET @SQL='CREATE TABLE ' + @myTable + '       
      (ID int NULL,
      [Volume] [int] NULL,
      [AdditionalData] [varchar](50) NULL
) ON [PRIMARY]'
EXEC(@SQL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.