There a two types of deployment
Project deployment model> deploying multiple packages
Package deployment model> deploying single package
You can deploy a package into a file system or database .
Example 2 Package deployment, Environments settings
Example 1:
Project deployment model, deploying 2 packages.
Package description:
1: Execute SQL Task (Prepare Tables), Sql statement.
IF EXISTS(SELECT 1 FROM sysobjects with (nolock) WHERE ID = OBJECT_ID(N'SampleData') AND type = (N'U'))
DROP TABLE [dbo].[SampleData]
GO
CREATE TABLE [dbo].[SampleData](
[id] [varchar](50) NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[Company_Name] [varchar](50) NULL
) ON [PRIMARY]
GO
IF NOT EXISTS(SELECT 1 FROM sysobjects with (nolock) WHERE ID = OBJECT_ID(N'tblLogs') AND type = (N'U'))
CREATE TABLE tblLogs(Id int identity, FilePath varchar(200), RecordsLoaded int, Dated datetime)
2: Data Flow Task (Load CSV File)
Row Count task is using @vCount variable and returns the number of rows extracted from Flat File Source.
Flat File Connection Manager: using expression / variable for [ConnectionString: @FilePath]
3: Execute SQL Task 1(Insert data into Log table)
Execute SQL Task is using Expressions
Property: SqlStatementSource
Expresion:
"insert into tblLogs
select '"+ @[User::vFilePath] +"', "+(DT_WSTR,12) @[User::vCount] +", getdate()
"
inserting values of two variables and date and time system variable.
Evaluated value:
insert into tblLogs
select 'C:\Files\TestData_1.CSV', 0, getdate()
PROJECT DEPLOYMENT
Step 1:
I want to create a project parameter pFilePath to pass this parameter to this SSIS Package.
The value of the parameter is: C:\Files\TestData_1.CSV
This package is loading the data form .csv file. When I deploy this package to server I will be able to change the File or File path on the server environment thanks to this parameter.
Step 2:
Go to vFilePath variable and in the Expression Builder assign the value of this parameter to it.
Step 3:
I am done with assigning the parameters to this project (I have two authentic packages).
Right click on the project name> Build : to check for any errors.
Step 4
On the server you need to have the catalog (database) for SSIS projects already created.
Step 5
Right click on the Project name > Deploy
Step 6
choose the destination server, if the Path is greyed out, click on the Connect button, next to the Authentication. Browse for the database catalog. You need to create a folder for this project.
Step 7
Press Next
step 8
Press Deploy
Step 9
Project has been deployed to server. You can click on any of these packages and execute them.
You can see the project parameter pFilePath and its default value. Here you can change the value and provide any other file with the same meta data and load it into the server table.
step 10
Report: You can see the Result, Duration, Execution Path [in which order the task were executed]
I have execute the package. In the second table, you can see the file path and file name, number of rows and date. Data are loaded into SampleData table, but this table is truncated before the execution of the package. Now I try to load some different flat file. I have TestData_.csv.
Now let's try to execute the package with a different flat file. C:\Files\TestData_2.CSV
To change the default value just click on the dots on the right side. After the new window pop up change the value in the bottom.
Now you can execute the package.
Example 2
Package deployment, Environments settings
Package Description
Data Flow
I have created a simple package which is loading the data from .csv to SQL server. I will create 2 project parameters for Server and Database because later on I would like to demonstrate how to change these values on Environments and load the data into different server (from the same flat file)
Project Parameters
Add Project Parameters: highlight Project.params, Open.
Create a parameter for server name and database name. As the values use Server and Database name. Require means: provide value on the execution time.
Property Expression Editor
I want to provide the values for Server and Database as an expressions using project parameters.
Highlight Server Connection Manager and press F4 to open Property window (1,2). In the Property Expressions find ServerName and assign pServer project variable to it. Similarly find InitialCatalog and assign pDatabase parameter to it.
Deploy Project to SQL server
Execute Package
To execute this package, right click on the Package, Execute. pDatabase parameter is Required. Click on the dots and use the default value from Package configuration in SSIS.
Post Execution
I have deployed and executed this package on the DESKTOP-N1E061P Sql serve, database: SSIS_Test. These are the default values from SSIS package.
Scenario
I need to change server name and database name so the executed package will load data into a different server, DB and table. How I could do this? (Using Environments)
Recreate the table on the new server. IMPORTANT: This new table needs to have the same name and structure as the previous table.
Create Environments and provide the new server and database values (names)
Reference the Environment variables in the Project Name
Execute the package.
1.Recreate the table on the new server.
As I said, this table needs to have the same metadata and name
2.1 Create Environments.
2.2 Properties
Go to the newly created Environment [Prod] > Properties, Variables. Use the new server name and database name as a values for the variables. After finish click OK.
3. Reference the Environment variables in the Project Name
3.1 References
Right click on the project name > Configure...
After the window will show up, go to References, click Add..
Reference the Environment name in the Local Folder(BikeStore)
3.2 Parameters
a) go to server name Value, click on the dots. In the new window, click on the Use environment variable. Find to variable referencing server name and click OK to load it. You must repeat the same for the database name, otherwise the values wont be proper referenced.
4. Execute the package.
As you can see I have no data on this new server (table) yet
4.1 setting up the execution
After you have clicked on Execute... this left-hand side window pop up. You need to check the Environment box on the bottom. That means, you are going to use the predefined values from Prod Environment. Then click OK to execute the package.
The data has been loaded...
