Import/export data from database to another

[ahm1985]

I have database have 200 table can i take data from 200 table by ssis and then put them in another database.

 

[xlbo]

do you want to take tables and table structures or just data?

If just data does the 2nd database have the same tables and table structures as the 1st database?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

yes first databases and second database have same tables but only i need data transfer from first database to second
please help me

 

[xlbo]

1: Is this a 1 time job or a recurring job?

2: does 2nd database alreadfy have data in it? If it has data in it are you wanting to remove current data and replace with data from 1st database or add data from 1st database to existing table in 2nd database?


In either situation, your 1st place to start would be to set up a query using the information_schema schema. this query will give you all the table names in the database:

select * from INFORMATION_SCHEMA.TABLES
where table_type = 'BASE TABLE'

You can put the results of that query into an SSIS variable (object ttype variable) and the set up a for each loop to iterate over each row returned - this will efectively set up a loop over each table of your database

you can assign the table name to a variable as part of the loop and use that to construct a query to take data from each table and insert into the table on database 2 as required

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

your answer is excellent but i need to more details about the answer like this if there are any website talking about it give me this sites
thanks

 

[xlbo]

There are lots of examples on google in terms of using the different components within SSIS

I would start step by step

1: Create a variable in an SSIS package - make it of type "object"

2: Create a SQL component in the SSIS package using the query I have given you

3: use the properties of that SQL component to send the results of the query to the variable you have created

Do that and execute the package - you will not see any results but package should execute without error - if it does, continue to add the For Each Loop component and so on

Remember - Google is your friend

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

OK if i need daily transaction data that made in first database send to second database can also make by ssis
and if you have external email to you send to me if possible
thank you very much

 

[xlbo]

Nope - this is not a personal service. I will not be giving you my email

I've given you more than enough info to make a start with. Try it out and post back if you have specific issues

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

ok i understand what you say im sorry to misunderstand according to my question if i use daily transaction data(transaction that made today only) how i make by ssis only this last question if possible
thanks

 

[xlbo]

All depends on how the data is set up. You need to either have a date field to use for each table or implement change data capture (google it) on your database. This comes down to database architecture and is not really an ssis issue

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

what i need is to use date field to use for each table meaning i have posted date this field found in all tables that i need to take data from it daily so that when this column his value equal today capture data only this what i need

 

[xlbo]

Ok so set that up in your data extraction query. You can use a variable in ssis to store table name and another to store whole SQL query e.g. "Select * from " +@table_variable+ "where posting_date = '" + @date_variable + "'"

Use the information_schema as previously described to get table names in for loop
Then calculate your date variable either in SQL using getdate() or ssis using today()

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ahm1985]

Ok if i need to take different data only from database to another what query i must do
thanks

 

[xlbo]

Please rephrase - that doesn't make sense

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question