I'm hoping someone out there can shed some light on this issue. Not after a solution as we have the process working but looking for insight on why this occurs.
I have a SQL 2005 SSIS (this is not an issue with SSIS but rather in the underlying SQL Database) process that extracts data based on customer specifications. Since every customer’s extract is different and can change month to month we have had to create a very dynamic process where all of our queries and table structures are created on the fly. We added a new file group that spans multiple drives and have set this file group to be the default.
Since SSIS executes a validation phase as part of the package structure we have some template tables that are used these tables exist on the primary file group and never contain data as during the execution of the SSIS packages all sources and destinations are replaced by values passed in as variables.
Now the problem the tables that get created as part of this process are done so by the process executing a Create Table script and in this script the file group is set to DEFAULT at this point no other table in the database exists on this file group. Even though the file group specified is DEFAULT the result is the table being created on PRIMARY. If I move the template tables used only for validation to the DEFAULT file group then the process creates the tables in the correct location.
As I said the problem is not the SSIS execution as I have taken one of the scripts executed by the process and ran it in SSMS and it creates it on the wrong file group as well. I would think that it shouldn’t matter where the various tables are as the SQL script creates tables that are independent of each other. The only thing I can come up with is that the file group is empty so it somehow gets over ridden when the create table executes.
Thanks!
I have a SQL 2005 SSIS (this is not an issue with SSIS but rather in the underlying SQL Database) process that extracts data based on customer specifications. Since every customer’s extract is different and can change month to month we have had to create a very dynamic process where all of our queries and table structures are created on the fly. We added a new file group that spans multiple drives and have set this file group to be the default.
Since SSIS executes a validation phase as part of the package structure we have some template tables that are used these tables exist on the primary file group and never contain data as during the execution of the SSIS packages all sources and destinations are replaced by values passed in as variables.
Now the problem the tables that get created as part of this process are done so by the process executing a Create Table script and in this script the file group is set to DEFAULT at this point no other table in the database exists on this file group. Even though the file group specified is DEFAULT the result is the table being created on PRIMARY. If I move the template tables used only for validation to the DEFAULT file group then the process creates the tables in the correct location.
As I said the problem is not the SSIS execution as I have taken one of the scripts executed by the process and ran it in SSMS and it creates it on the wrong file group as well. I would think that it shouldn’t matter where the various tables are as the SQL script creates tables that are independent of each other. The only thing I can come up with is that the file group is empty so it somehow gets over ridden when the create table executes.
Thanks!