May 26, 2010 at 9:23 am
I'm just getting going with SSIS. I would like to copy large numbers of tables from a Production to a test environment.
I know that I can set up each of the tables in a data flow but there are quite a number of them. I thought of setting up a seperate data flow for a group of related tables but I'm thinking there must be an easier way.
I noticed that for "OLE DB Source" and "OLE DB Destination" object there is an "data access mode" option for "Table name or view name variable". My thought is that if I set up a "Foreach Loop container" that passes a tablename to the Dataflow and have the OLE DB Source and Destination set up for a Table name variable that I could put together a list of all the tables that I want to process.
My main concern will this work because the tables each have different columns.
Steve
May 26, 2010 at 10:34 am
The mappings between source and destination columns MUST be done at design time, this makes what you are proposing difficult with the provided controls. I believe CozyRoc has a custom control that can do this.
However, I have a better solution. Use the import/export wizard and set the source and destination and then select the tables you want to copy off the list. Here is the most important step.. Choose the box that says to save the package. This will provide you the basis of what you need and make the tools do most of the work..
CEWII
May 26, 2010 at 1:20 pm
CEWII,
I was afraid of that. I'm sure that the wizard is quicker. I wish that it would default to delete existing rows instead of append so I wouldn't have to edit each of the tables mappings (more than 300). I would also like it to Enable Identity Insert whenever there is an Identity field automatically.
Also, if there is any Foreign Keys, TRUNCATE TABLE doesn't work for the table with the Primary Key referenced and DELETE FROM needs to be used instead.
Thanks,
Steve
May 30, 2010 at 9:50 am
I've just battled through this - there's no way to parameterise loading tables in the way you suggest. In fact, I think SSIS makes it more difficult than it needs to be - I felt like I'd end up with a maintenance nightmare if I did this in SSIS.
In the end, I load all the data using linked servers and insert into select... (and select into to construct the tables) to get the data into a staging area. Then SSIS to clean it up and load it. This is also good because you can do quite a lot in the initial select to get the data into the staging area that then makes the cleaning/validating in SSIS more natural and straightforward - plus linking the source data to an audit table is also easy.
HTH
Miles
June 1, 2010 at 1:00 pm
After some research and trial and error, I plan to use the following steps:
1) Using the Import / Export Wizard and select all tables with the CheckBox
2) Edit Mappings and select 'Enable Identity Insert'
3) Uncheck any tables not desired and all views
4) Save SSIS Package and give it a name (automatically Package1.dtsx if run from BIDS)
5) Open Package with BIDS
6) Add a Sql Task before the data flow tasks (all the quotes are 1 or 2 single quotes). ** edited code **
Exec sp_MSForEachTable
'
ALTER TABLE ? NOCHECK CONSTRAINT all
ALTER TABLE ? DISABLE TRIGGER all
IF object_name(Object_ID(''?'')) NOT IN
(''table1_not wanted'',
''table2_not wanted'',
''table3_not wanted'')
BEGIN
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'') = 1
Delete From ?
Else
Truncate Table ?
END
'
7) Add a Sql Task after the data flow tasks
Exec sp_msforeachtable
'
ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all
ALTER TABLE ? ENABLE TRIGGER all
'
The reason I disable triggers and re-enable them after loading the tables is so the triggers will not add rows into the audit trail tables when rows are inserted into the tables.
Steve
June 2, 2010 at 11:18 am
After more work, I found the Preparation Task needed to be changed.
Exec sp_MSForEachTable
'
ALTER TABLE ? NOCHECK CONSTRAINT all
ALTER TABLE ? DISABLE TRIGGER all
'
GO
Exec sp_MSForEachTable
'
IF object_name(Object_ID(''?'')) NOT IN
(''table1_not wanted'',
''table2_not wanted'',
''table3_not wanted'')
BEGIN
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'') = 1
Delete From ?
Else
Truncate Table ?
END
'
GO
I needed to seperate disabling contraints from the clearing out of the tables.
Steve
June 3, 2010 at 2:55 am
Another suggestion that might be useful...
If you plan on doing the Production to Dev copy on a regular basis, you could setup a link server from DEV, pointing to the live database, and just use some basic T-SQL, like truncate table & insert into, using a similiar SP as you already have, looping through the tables in the dev db, which you could set up in an SQL Server job, avoiding the need for an SSIS package.
The main thing that would need to be done is the initial copy of the required tables from Prod -> Dev.
dont forget if your not doing and transformations on your data SSIS, a T-SQL select could be a lot quicker....
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply