November 2, 2007 at 1:55 pm
I am working on a ssis package. Tables created in the SSIS are named in the MMYY format using the datepart function. I need to determine the year of the table and then put it in a filegroup accordingly. Like tables with '07' in the name will go in a 07 filegroup.
I can query the database and get a list of tables for 07
I am thinking this can be done in 2 ways
1.I can place the table in filegroup using the ‘ON’ clause using a variable (containing the current year) when I create the table. This is not working when I try using the variable below it creates a filegroup by the name ‘@fileg’.
declare @fileg varchar(60)
set @fileg = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
create table test (id int) ON [@fileg]
2.I can create a bunch of filegroups and have a step in the package where a query can be used to identify tables with ‘06’, ‘07’ and so on and then place them in the required filegroups. I found one way was to use the 'drop constraint move with' option but that is working with primary key but not with check constraint
That is all the info i have been able to get so far. Does Please help. Are there any other ways of doing in besides an execute SQL Task in SSIS?
November 4, 2007 at 10:13 am
Are you saying that you are moving files as in data files to new filegroups? Or are you talking about data?
It's not clear exactly what your package is supposed to do. If this is an SSIS specific question, please post in the SSIS forum. If it's regarding moving data between filegroups with T-SQL, then please elaborate on what you're doing.
November 7, 2007 at 12:30 am
Hi
From what I understand you wish to create a table for per month of a year. In otherwords you would like to partition the data into smaller tables to have faster access to a smaller subset of data.
If you are using SQL 2005 Enterprise you could use the new Partition Functions and Partition schemes functionallity added in the Server. Basically this will allow you to Partition 1 logical table into several physical tables. For more information on Partition functions please look at the BOL. Search for create Partition Function statement.
If you go the SSIS route there is some things I would suggest. In your first piece of code why not add a filegroup to the database first using a ALTER DATABASE statment then create the table on the newly added Filegroup. You need to add a filegroup to the database before you can create a table on it.
The second piece of code will work but be aware if you have a large amount of data and you use the MOVE WITH option it will lock the table nice and tight while it moves the data from one filegroup to another.
Best advise I can give here if you are running enterprise look at the Partitioning Functions built into SQL they are extremly powerfull and easy to use.
Hope this gives you some ideas.
Later
Neil
MXIT International SQL Specialist.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply