Solved

'Export and Import Wizard' from BIDS

Posted on 2010-08-16
15
453 Views
Last Modified: 2013-11-10
To implement the same functionality of 'Export and Import Wizard' in BIDS as a package, is there a way to simplify by doing something for all tables, in one shot, instead of creating a flow for each table?

thanks
0
Comment
Question by:anushahanna
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 84 total points
ID: 33449068
NO..
If you are not using any Advanced Transformations.. then Use Import/Export wizard and Select all your Source/destination Tables and In the Last window Check the Box - SAVE AS SSIS PACKAGE and later open with BIDS, to make changes if necessary or for rerunning the same ETL
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 346 total points
ID: 33450883
As I understand from your question you need Dynamic Data Flow. you want to create a data flow which can be run for every source and destination table/database/server. Am I right?
if yes, there are hints to consider:
1- SSIS has ability to do a level of dynamic data flow. but in this level your source and destination should be in same structure at all. when I talk about same structure, I mean same fields, same number of fields, same data types. if you want to designed a data flow to transfer data from source table to destination table, the Meta data of columns in data flow is Static, so if you want to use it for another source/destination, fields of this new source/destination should be exact same previous source/destination.
If you meet this conditions, then you can create dynamic data flow, simply by using EXPRESSIONS to set connection string of source and destinations. probably people do this with a foreach loop, loop through sources and destinations and set data flow connection strings dynamically there.

2- If first way isn't proper for you, this means that if data structure of your source/destinations is different, you should try another ways, maybe creating insert into statements dynamically can be a way, and running them by execute sql task ( source and destination needs to be linked in this way ) . OR you can use Transfer sql server objects task and select only tables ( source and destination needs to be on sql server in this way ) . and any other ways you can use for this.

did you mean this exactly? let me know if you mean anything else

0
 
LVL 6

Author Comment

by:anushahanna
ID: 33589358
vdr1620, Import/Export wizard has an issue for me. that is why i needed to avoid it, and try another option in ssis directly.

thanks
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33589366
reza_rad, in my case, the first situation fits. would you have any example you have already done for the foreach/EXPRESSIONS idea,.

thanks
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 346 total points
ID: 33605589
I can provide an example but this is strongly based on what you want to do exactly?
dose your source and destination databases are same at all but only table names change?
or table names are same but source/destination databases change?
or both change?
please tell me what exactly you want to do and I will help you in detail.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 33629917
anushahanna: what's your issue with the Export and Import Wizard, perhaps you need to address any problems, it could be easy and saves you the hassle...

do you get and error msg?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33747343
reza_rad, thanks for your help:

source and dest DB schema are exactly identical, but names are also same, but are on 2 different servers. table/column names are same too, only data may differ.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anushahanna
ID: 33747364
huslayer, this is in relation to
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_26360883.html

because i do not get access to the box with SSIS installation easily, I am delayed in implementing this quicker. but will get to it very soon.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 346 total points
ID: 33751774
so I suggest to :
start with an import / export wizard, set source and destination, select all tables to transfer, set mappings.
all of this was like before, except this one:
save your packages at last steps to a file location.
then open this saved file with BIDS, after creating a ssis project,
right click on ssis packages folder, and select add existing package, select saved package from there.
then you can see that wizard created ssis package for you. please take an screenshot of the package and put here, I will help you in next steps then

0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 70 total points
ID: 33753341
OK, from the other referenced question... you said

in Database1, the Identity PK was 1,2,3,5
In Database2(Destination), the PK is being populated as 1,2,3,4

start with an import / export wizard, set source and destination, select all tables to transfer.

in the mapping, ignore the ID column, you can't import an ID.
see attached...

hope that helps.

ID.jpg
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33863565
Thanks very much for your patience.. got a few minutes on this box.. see attached..
SSIS.bmp
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33863572
huslayer,

why ignore it?

>>
in Database1, the Identity PK was 1,2,3,5
In Database2(Destination), the PK is being populated as 1,2,3,4

we need same order- if you ignore it we will get 1,2,3,4 which is what is happening now (is identity field)
0
 
LVL 30

Accepted Solution

by:
Reza Rad earned 346 total points
ID: 33863700
Hi,
as you see in your screenshot, you have only two OLEDB connections in connection managers section,
one for Source, another for Destination,
you should right click on them , go to properties window, then find expression property,
click on it and property expression editor will open, then you can set Connection string, servername, username ,... with variables.

this will let you to change source and destination connections by changing variable values simply even outside of package.

does it make sense now?




0
 
LVL 6

Author Comment

by:anushahanna
ID: 33947558
got your point- thanks very much.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33949841
glad to help
Regards,
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now