Solved

Creating SSIS packages with SQL Server Business Intelligence Dev Studio

Posted on 2014-02-03
9
294 Views
Last Modified: 2016-02-10
I am working on developing an SSIS package that will grab data from a number of views on a source database and copy that data to destination server, truncating the destination tables and inserting new data from source database views.

For that purpose I used Business Intelligance Studio to create an Integration Services Project and then I added SSIS Import/Export wizard.

I have a couple of questions:

1) Is using wizard the best way for my purpose?

2) When creating the SSIS package, I use my development machine but later will need to put this package on two different servers: QA server and production. How do i go about doing that?

3) When moving package from my development machine onto a  QA server and then to production server, the name of the source and target databases will change. How do I handle the change?

What I mean is on my dev machine, while developing the package, for test purposes I use "server1" as source server name and "localost" as the target server name, but on my QA server I will need to use "Server03" as the source server name and "ServerQA" as the target server. And on Production server the names will also differ. Hope my explanations are clear. How do I modify the names in my SSIS package?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 500 total points
ID: 39830938
Hi,

1) Is using wizard the best way for my purpose?

Yeah, I sometimes use the wizard, this may flesh out the package for you, but you'll probably need to tweak it along the way too.

2) When creating the SSIS package, I use my development machine but later will need to put this package on two different servers: QA server and production. How do i go about doing that?

In the solution where you have been developing the package you'll see the .dtsx file.
I generally open SSMS in the Integration Services mode and then add the package to the server by Importing Package from file.

3) When moving package from my development machine onto a  QA server and then to production server, the name of the source and target databases will change. How do I handle the change?

You can achieve this by using an SSIS Package Configuration File.


Each one of these steps I believe will spawn many other questions however.
It is quite an involved process to get a whole SSIS package up and running.

T
0
 
LVL 35

Author Comment

by:YZlat
ID: 39831004
Thank you for your fast response!

>>I generally open SSMS in the Integration Services mode and then add the package to the server by Importing Package from file.

I am new to this so could you please elaborate?
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39831111
When you open SQL Server Management Studio, you get presented with a logon screen.
Most likely you'd see "Database Engine" in the Server type.

Drop down the list and select Integration Services.

The rest will all be dependant on the setup and rights assigned to your login...
As you'll know this can be a bit of a minefield.

T
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 35

Author Comment

by:YZlat
ID: 39842080
I changed dropdown to Integration services and it is asking me for a server name. Would it be my QA Server name?
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 500 total points
ID: 39842844
I hope I'm interpreting where you are up to already.

So, I assume you have a package created and tested in BIDS.
So the deployment to QA is next.

Therefore, you need to import the package onto your QA system that has SSIS running. I assume this is the "ServerQA" machine.

T
0
 
LVL 35

Author Comment

by:YZlat
ID: 39857265
I will try it as soon as I fix up my configuration file
0
 
LVL 35

Author Comment

by:YZlat
ID: 39860270
OK, I followed your instructions and logged in ro ServerQA via Management Studio (changing Database Engine to Integration Services).

There are two folders: Running Packages (empty) and Stored Packages(has 2 subfolders File System and MSDB).  I expanded Stored Packages and then File System, created new folder called "SSIS" and right clicked on it and selected Import package.

Then it prompted me for some information. I selected File System as package location and navigated to .dtsx file. So now the package is imported to the QA server.

Next thing is my package connections ar setup to use destination database on my dev server. Please instruct me on how to change it to QA server information.

I tried creating deployment package and modifying info in configuration file but when I ran the package on QA server, it uses the configuration file from Dev machine "the package is attempting to configure from the XML file "C:\...\.dtsConfig". And it fails since I changed the configuration file to read from tnsnames entry existing on the QA server, which is different from the one on development machine. How do I ensure my package reads tnsnames.ora on QA server?
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39868322
I guess you lost interest in the question. Thanks for your help so far
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39868337
Sorry, I did and I didn't lose interest.
I have never used config files, I had been looking in my spare time for a good guide on them.
Spare time is rare these days with work, kids and study for SQL 2012 exams...sigh!.

Sorry again.

T
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 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