SSIS Project creation remains same from SSIS 2008 to SSIS 2012. To create SSIS project file, we need to open BIDS and choose Integration services .If you are new to SSIS , please go through basis of SSIS project creation and package development
In my example, I have two similar tables (CountryDetails) in two different databases (SampleDataBase1, SampleDataBase2). I created one simple package, logic is to insert one row to CountryDetails table in either of SampleDataBase1, SampleDataBase2 databases based on environment configuration
Table Structure:
CREATE TABLE [dbo].[CountryDetails](
[ID] [float] NULL,
[StateProvinceCode] [nvarchar](255) NULL,
[CountryRegionCode] [nvarchar](255) NULL,
[Name] [nvarchar](255) NULL,
[TerritoryID] [float] NULL
To use connection string as dynamic, we need to configure the Expression property of the Connection manager and give the value of ConnectionStringas Parameter DataBaseConnection value in Expression
Now if we execute our SSIS package, data will insert into SampleDataBase2.CountryDetails table because it overwrite connection string from project parameters to package connection string
Now change the database Name SampleDataBase2 to SampleDataBase1 in project parameter DataBaseConnection and execute it again. Now data is inserted in SampleDataBase1.CountryDetails table
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
