November 5, 2007 at 9:12 am
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 placing them in yearly filegroups 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 5, 2007 at 10:49 am
You need to use dynamic SQL. Something like this:
declare @fileg varchar(60), @Sql varchar(500)
set @fileg = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
Set @Sql = 'create table dbo.test (id int) ON [' + @fileg + ']'
Exec(@Sql)
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
November 6, 2007 at 7:07 am
Thankyou for your help.. i tried using dynamic sql for creating a filegroup as a task in the beginning of the package but that is giving me an error now..could you plz give me some kind of direction on how i can solve this:
declare @newfilegroup varchar(60)
set @newfilegroup = substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)
this gives me a synatx error where as
declare @newfilegroup varchar(60)
set @newfilegroup = 'a' + substring(CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())), 3, 4)
EXEC ('ALTER DATABASE test ADD FILEGROUP ' + @newfilegroup)
works fine
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply