Creating SSIS packages with SQL Server Business Intelligence Dev Studio
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I changed dropdown to Integration services and it is asking me for a server name. Would it be my QA Server name?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will try it as soon as I fix up my configuration file
ASKER
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?
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?
ASKER
I guess you lost interest in the question. Thanks for your help so far
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
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
ASKER
>>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?