Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articl...b9cc0d4526e1913

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articleprint.aspx?quickid=0301101&printmode=true
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/1f4dd406fa18d676/8b9cc0d4526e1913?lnk=st&q=scripting+sql+server+indexes+create+drop&rnum=16&hl=en#8b9cc0d4526e1913

Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

Follow

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..
Hi
http://www.microsoft.com/sql/techinf...calability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>

DW/DSS Schema design

I know a data warehouse stores historical data and the fact and dimension
tables could be huge with billion rows and could be more than 100s of GB in
size for just one table. Can someone share some design tips on whether they
partition those fact tables based upon time and then maybe use some
partitioned views to access it.
Im just looking at it from performance and also scalability.. and if we do
create those multiple partitioned tables on different filegroups stored on
different drive.. This is just one way I can see it done.. Is this approach
right and do people use it ? Also, what other ways can we scale and have
better performance. Any good DW design URLs i can refer to..
Thanks a lot in advance..Hi
http://www.microsoft.com/sql/techin...scalability.asp
"VLDB's"
http://www.microsoft.com/sql/evaluation/bi/default.asp "Implementation
Considerations"
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#gnvjMMLFHA.2120@.TK2MSFTNGP10.phx.gbl...
> I know a data warehouse stores historical data and the fact and dimension
> tables could be huge with billion rows and could be more than 100s of GB
in
> size for just one table. Can someone share some design tips on whether
they
> partition those fact tables based upon time and then maybe use some
> partitioned views to access it.
> Im just looking at it from performance and also scalability.. and if we do
> create those multiple partitioned tables on different filegroups stored on
> different drive.. This is just one way I can see it done.. Is this
approach
> right and do people use it ? Also, what other ways can we scale and have
> better performance. Any good DW design URLs i can refer to..
> Thanks a lot in advance..
>