A data source in SSIS package is something which helps us to connect to the source of data like RDBMS, CSV File, Excel File, etc. and pull the data out.

A data source is used when we’re using Data Flow task. I’m sure you know about the Data Flow task, which is widely used, almost in every package. :)

There are various data sources such as ADO.NET, OLE DB, Flat File, Excel File, Raw File, etc. We choose a data source based on our requirement, from where we’re going to pull the data out.

Today, we’ll be talking about OLE DB Source, which can connect to almost all kinds of existing data sources, whether it’s SQL Server or Oracle or SAP NetWeaver or Access, etc. OLE DB uses the Connection Managers behind the scene in order to connect to a data source. It’s not Data Source but a Connection Manager decides what kind of driver you’re going to use and what database or data source you’re connecting to.

We’re going to discuss how to export data into different spreadsheet in the same Excel file over here. We’re going to use SQL Server Integration Services 2012 and AdventureWorks2012 database. However, if you’re using earlier version like SSIS 2008 R2, It’ll be working perfectly fine. So, no worries!

I assume that you know how to create a package, what is Data Source, Data Destinations, etc. in SSIS.

Create a package and drag and drop a Data Flow task on Control Flow tab.

Today, we’re going to discuss how to implement One-to-One relationship between two tables in SQL Server. So, what does it mean? That’s pretty simple. We can simply go to Database Diagram option and drag Primary Key from master table and drop that on Foreign Key on child table…and that’s it. What’s the big deal???

Yes. It will definitely implement Foreign Key constraint but the cardinality will be One-to-Many by default. Here we get the problem. I wish to implement One-to-One cardinality rather One-to-Many. I tried a lot with available options but in vain. The SQL Server doesn’t provide any option where you can simply go and change the cardinality between tables.

Let’s discuss how to achieve this in SQL Server.

I’ve got two tables, named 1.) Customer, and 2.) CustomerContact as you can see in below image.

It’s pretty common requirement for a developer to make a text box as Read-Only or completely Disable it in an application, whether it’s ASP.NET web page or MVC view or any other technology. It’s a kind of user interface’s requirement.

For example, user must not be able to update/delete a Customer ID or Product ID when we’re populating details into Edit view or Details view on a web page. So, how to implement this in MVC4?

Lets say we are having a simple CustomerModel class as shown in below image.

I’ve always come across with pretty common questions while dealing with SSIS training sessions – What are Data Sources, Data Transformations and Data Destinations in SSIS package? Why do we use them? Where do we use them? Do we use them all the time? blah…blah…

So, let’s discuss a little bit about them.

I’m sure every package developer comes across requirements where we need to import/export data from one data source to another data source. And, the very basic control/tool that SSIS provides with us is Data Flow task. I assume we are aware of why we use Data Flow task. Yet, let me briefly explain it.

The Data Flow task is a tool which is used to define activities involved into import/export data from one source to another. We need to make connection with the data source, pull the data out, work on the data like cleaning or validating it and then push into data destination. Here comes the need for Data Sources, Data Transformations and Data Destinations.

The Data Sources such as ADO NET, OLE DB, Flat File, Raw File, Excel Source, etc. help us to connect to the data source, from where we are going to pull the data out. These Data Sources use connection managers internally to define the type of data provider being used, server name, database name and login credentials, etc. in order to connect to a data source.

I got confused when I tried SSIS 2012 for the very first time. In earlier versions of SSIS i.e. 2008 or 2008 R2, we have only a single Toolbox window in the package designer. While, the SSIS 2012 provides all tools such as Data Source, Transformations, Data Destinations, etc. in a new windows called SSIS Toolbox. I’m unsure why the SQL Serve team has done this and what they wanted to achieve by this. I guess they thought it would be easier for a package developer to have all tools and controls in a separate toolbox window from management point of view. To me, that’s okay..:)

So, if you are not getting SSIS Toolbox window in your Visual Studio, where to find it out?

What all you need to do is go to View -> Other Windows -> SSIS Toolbox. Click on it and it will appear to the left side of Visual Studio by default. You may dock it anywhere, if you wish. Don’t get confused when you see empty original Toolbox window over there as well. It doesn’t contain any SSIS tools.