Copying indexes from ODBC Source To OLE DB destination (Sql DB)

Question

Hi,

I am somewhat new to SSIS but have managed to create packages , data flows and copy tables and data from ODBC source to a sql database. I am at a point where i need all the indexes defined which are in the source in my sql database. Given the number of tables i would hate to manually recreate all the indexes in Sql database which exists in the source ODBC database.

What would be the best way to copy all the indexes over to the sql database ??

Answer 1

First, the big question I have is why you want to recreate all of the indexes? I assume that the ODBC source is not SQL Server.  Thus, it is very likely that SQL Server is not going to use all of the old indexes the way that the original database server did. And SQL Server will use indexes that the other database server didn't use.

Second, what is your database server?  Each server type has its own way of listing indexes.  Almost ten years ago I mistakenly extracted all of the indexes from Access to recreate the indexes in SQL Server.  It involved some DAO code to create lists of tables and their columns.  I then created a set of create index statements (http://msdn.microsoft.com/en-us/library/ms188783.aspx) from the table and column information.  I imagine most database products that use indexes have a way of generating such a list.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

You are right about the ODBC source NOT being a sql server db. The indexes in the source define primary ,unique and other indexes defined to enhance performance which i would like to replicate in the destination sql server 2008 r2  staging database.

I could not find a utility in BIDS to transfer index information.Probably many of you may have encountered this issue earlier and would appreciate any help in getting the index information to the destination db.

Answer 3

Hi RPVS32 ,

I agree with Russ's suggestion,different database products have differnet index schema or otherelse, we can only transfer index between instances of SQL Server using Transfer SQL Server Objects Task in SSIS. If you have any concern about this, please submit a feedback at http://connect.microsoft.com/SQLServer/Feedback 

Thanks,
Eileen

---

Eileen Zhao
TechNet Community Support