Deploying SSIS packages into SQL Server vs Deploying as a File
In earlier days we have the options to save the DTS packages to SQL Server or to File System. Many of you trying to understand the trade offs between them. This post list out the advantages of both options.
Advantages of Saving and Deploying SSIS packages to File System
- We can use Source control system manage the versions.
- More secure when using the encrypt with user key encryption.
- Packages are available when you save locally. not subject network downtime problems.
Related Posts:
1 commentDeploying SSIS packages into SQL Server vs Deploying as a File
In earlier days we have the options to save the DTS packages to SQL Server or to File System. Many of you trying to understand the trade offs between them. This post list out the advantages of both options.
Advantages of Saving and Deploying SSIS packages to File System
- We can use Source control system manage the versions.
- More secure when using the encrypt with user key encryption.
- Packages are available when you save locally. not subject network downtime problems.
Related Posts:
No commentsUsing For Loop and Foreach Loop Containers in SSIS
| SQL Server Integration Services supports two container tasks name For Loop and Foreach loop. These containers supports to execute the repetitive workflows in controlled way. The For Loop has three expressions that control it’s execution and termination. This post also discuss about Foreach loop container in SSIS. |  |
The three expressions are
InitExpression The For Loop evaluates the InitExpression exactly once at the start of the loop execution. The initialized variables are used later in other expressions.
EvalExpression The For Loop evaluates the EvalExpression once for every iteration of the For Loop.For Example The InitExpression would be @Index = 1 and EvalExpression would be @Index > 1. EvalExpression must use boolean operators.
Related Posts:
1 commentUsing Data Profiling Task in SSIS
| Data Profiling is important in data quality processing and transformation. It provides a way to investigate large datasets and to identify incorrectly formatted data. This post explains doing data analysis using Data Profiling task. | |
Data Profiling task helps you to
- Identify the problems with your data. Example: Finding the products in your data which are not following the naming pattern.
- Identify the missing functional dependencies between columns. Example: State and Zip code columns. It helps you identify wrong ZIP code that has been entered for a state or city.
Setting up Data Profile Task
Related Posts:
2 commentsSSIS Package Configuration in SQL server 2008
SSIS configuration wizard allows you to create configurations for packages. It also allows you to update the properties and objects of the package at run time.
Package Configuration Benefits
- Configuration really helps the developers to smoothly move the packages from Development environment to Production environment.
Eg: Configuring the source path of the file or updating the database
connection string.
- Configurations makes it easier when you deploy the packages over different sql servers.
- Using property expressions we can update the value of variable in configuration. More about this can be read here
1. Open the Business Intelligence Development Studio from Start->Programs
Related Posts:
1 commentRuntime Settings with SSIS Package
Introduction
Clients may require to integrate the SSIS packages in their environment(Production). SSIS package settings needs to be changed at run time and all settings needs to be pulled from the database using set of stored procedures.
1. Create a SSIS package and drag the Execute SQL task to control flow in the package as follows
Related Posts:
1 commentETL Process using SQL server SSIS
This post explains about creating a SSIS package for demonstrating the small ETL process using transforms and connections in SQL server 2005.
1. Create a new SSIS package and drag a DataFlow task onto the control flow. Double click the task to go to the Data Flow tab.
2. In the Data Flow tab, drag an OLEDB Source onto the design pane. Select the data connection to Adventure Works Database.
3. Make sure that the Data Access Mode option is set to “Table or View”. Select the [Production].[TransactionHistoryArchive] table from Table drop-down box.
4. Select the ProductID,Quantity and ActualCost columns and say OK to exit the editor.
5. Drag a Derived Column transform to dataflow and connect the dataflow task to this task.
6. Double-click on the Derived Column transform to open the editor and type the new column with name TotalCost and configure the column as follows and click ok to exit the editor.
7. Drag an Aggregate transform onto the data flow and connect the arrow from Derived Column transform onto this transform. Double click the editor to configure the properties.
8. Drag a Sort transform onto data flow pane, connect Aggregate transform to this transform by the green arrow. Double-click on transform to configure the properties. Click OK to exit the editor.
9. Now we export this data to a flat file so that interested parties can read the file. Drag a Flat File dataflow to pane and connect the Sort transform by using the green arrow.
10. Double click on Flat File transform to get the following editor.
11. Now execute the package and you should be see the rows flow through the package as shown below
Conclusion
In this post, we have seen how transformations in SSIS allows us to change the data from a source and pass the results as output to a destination or another transform.
Related Posts:
1 comment
