INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Import/export data from database to another

Import/export data from database to another

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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

(OP)
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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

(OP)
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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

(OP)
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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

(OP)
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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

RE: Import/export data from database to another

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

RE: Import/export data from database to another

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: How to get the best answers before you ask a question

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close