Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
Newbie question, moving data between sql server 2000 and 2005
Hot Topic
- Select with group
- Cannot get Stored Procedure parameters in SMO
- Can create login but cannot connect
- Grouping data by time period
- Updating Sybase From SQL Server
- How to calculate value for InteractiveHeight Property??
- Window identity of SQL Server
- Empty report: displayed on server 1, error on server 2
- SQL Configuration holding connections to DS
- Error installing SQL Server 2005 Upgrade Advisor
SQL Server
- Books on SSAS 2005....
- Backup File I/o Error
- Importing text file removes decimal separator
- How can I put a newline within a string column?
- SQL Server Enterprise will only use 128MB Ram, whilst we have 16GB available
- Dynamic measures
- New Java driver for SqlServer
- Mising Text with expressions
- Using a sproc as a source within Dataflow
- Problems with remote SQL 2005 server and excel as an input source
Newbie question, moving data between sql server 2000 and 2005
guoguowenzi
So basically, this is what I do.
1. I create a new SSIS project and add an "Execute SQL" task wich I name "Create staging table" and enter the create script
2. Enter a dataflow task, and in the dataflow editor, i hook up a OleDB Datasource and a Sql Server Destination. In the properties of the destination I select the targeting database . At this point the staging table doesn't exist, so I press the "New..." button by the table/view field and enter the same create script from step 1. This generates the table so my project is happy. After this I map the columns from the source to the destination.
3. In the control tab I drag another "Execute SQL" task wich drops the main table i wish to replace.
4. I drag another "Execute SQL" task wich renames the staging table.
All of these are connected with the green "Sucess" arrows. I Execute this package and everything works fine. After the execution, the staging table is now renamed to the master table. If I execute the package again, I get the following error: Package Validation Error (details: Error at Data Flow Task [SQL Server Destination [1047]]: The metadata for "[dbo].[staging_customer]" cannot be retrieved. Make sure the object name is correct and the object exists.
Now, what I could do is run the create script (from step 1) last to create a new empty staging table for the next run, but wouldn't that be a wrong design What if someone dropped the empty dable between executions Shouldn't I have to be smart and begin by creating the new empty staging table in the beginning
Regards,
Atli
stevenpr_MS
Wow, That pretty much fixed my problem.
Thank you so much for your help.
Regards,
Atli
Daniel P.
Hi,
I guess your control flow is like this 'execute task to create staging table' --> 'data flow to load data' --> 'execute task to drop the main table and rename the staging table'.
If so, u must be getting a validation error in the data flow task. To avoid this, within data flow, select the properties of your target component and set the 'ValidateExternalMetadata' property to false.
pwtsky
Why does creating the table not work What error do you get
-Jamie