SQL Server Performance Forum – Threads Archive
Migrate MSExcel worksheet sources to SQL2005Hi, 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=’‘ />]<br /><br />DTS feature is found in Enterprise Manager of SQL Server
Hi,<br /><br />I just want to elaborate what I am about to do here to clarify things up.<br /><br />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.<br /><br />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?<br /><br />Please help. Thanks in advance.<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by smy</i><br /><br />welcome [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />DTS feature is found in Enterprise Manager of SQL Server<br /><hr height="1" noshade id="quote"></font 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?