SQL Server Performance Forum – Threads Archive

Migrate MSExcel worksheet sources to SQL2005

Hi, Is there anyway I could do to migrate my data on my excel workbook to SQL Server 2005 per worksheet? Meaning, I have to create table for all my excel worksheet then transfer all the data to a new table through SSIS package. How will I possibly do this? Please help. Thanks in advance.

You can use DTS (Data Transformation Services) to import the data.

Thanks for your reply. Where can I find this DTS feature in my VS2005 – SSIS package designer? Thanks again.

quote:Originally posted by smy You can use DTS (Data Transformation Services) to import the data.

welcome [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] DTS feature is found in Enterprise Manager of SQL Server

Hi, I just want to elaborate what I am about to do here to clarify things up. In my case, I have 50 worksheet in my Excel workbook and I would like these worksheets be transferred in our SQL Server 2005 database, one table per worksheet. Now, I am planning to use SSIS "Foreach" control block so that SSIS recusively gets data on every worksheet, then SSIS package will transfer the data gathered on one table to another. Is this task possible in SSIS? If not, kindly advise me of a more suitable solution for this. Or, is there any available custom script/coding for this in VS2005? Please help. Thanks in advance. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by smy welcome [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] DTS feature is found in Enterprise Manager of SQL Server <hr height="1" noshade id="quote"></blockquote id="quote">

I had not tried using SSIS. In SQL Server, DTS can do all these importing painlessly. Its a powerful tool to use for migrating Excel worksheets to the SQL Server easily. You can also create the DTS package and save it..then write a batch file to run it if you intend to run it later.

Will this DTS could recur on my 50 worksheets and create new tables for them? I could not find similar article/information related to my problem. Please help how can I build DTS package that could do this task (migrate 50 worksheets to SQL tables, recusively). I hope you could help me out with this. Thanks again.

no. as long as you drop and create tables, the tables will not be duplicated.
http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/ hey, check this out:http://www.sqlservercentral.com/columnists/bknight/upgradingsqlserver2000dtspackagestossis.asp you are using SQL Server 2005?

]]>