SSIS Connection Sql Server

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.

Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations, graphical tools for building packages, and the Integration Services Catalog database, where you store, run, and manage packages.

You can use the graphical Integration Services tools to create solutions without writing a single line of code. You can also program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

Integration Services (SSIS) Connection

Microsoft SQL Server Integration Services packages use connections to perform different tasks and to implement Integration Services features:

  • Connecting to source and destination data stores such as text, XML, Excel workbooks, and relational databases to extract and load data.
  • Connecting to relational databases that contain reference data to perform exact or fuzzy lookups.
  • Connecting to relational databases to run SQL statements such as SELECT, DELETE, and INSERT commands and also stored procedures.
  • Connecting to SQL Server to perform maintenance and transfer tasks such as backing up databases and transferring logins.
  • Writing log entries in text and XML files and SQL Server tables and package configurations to SQL Server tables.
  • Connecting to SQL Server to create temporary work tables that some transformations require to do their work.
  • Connecting to Analysis Services projects and databases to access data mining models, process cubes and dimensions, and run DDL code.
  • Specifying existing or creating new files and folders to use with Foreach Loop enumerators and tasks.

Connecting to message queues and to Windows Management Instrumentation (WMI), SQL Server Management Objects (SMO), Web, and mail servers.

Lets get start…!!!

Create connection manager

First of all open you SQL Server Data Tools for Visual Studio you already install, under the File menu choose New Project and you can see many option in dialog box and Integration Services Project,  like what you can see on picture below

SSIS  - Connection Manager

Btw, I use Visual Studio 2013 for my project.

And you can change location for your solution, in project I choose Drive D for location solution, you can change to everywhere that you want and here what you get after click button OK

SSIS  - Connection Manager

Add Connection

In that package you can see connection manager in the under bottom pane and there is some kind of instruction that you should to do, so you can follow that instruction and then there will be dialog box appear in your screen.

SSIS  - Connection Manager

So as you can see, there are some options you can choose for connection but in this article we will choose New OLE DB Connection for our connection, for others connection on that lists we will discuss later but now we are focused on OLE DB Connection.

SSIS  - Connection Manager

In that dialog box you should choose what server and database name for your connection, after that click button Test Connection to make sure that your connection successfully connected.

File Connection

After you getting connection well, next you make connection for File Flat, what you should to do for create that connection is similar with create OLE DB Connection, you just right click on bottom pane and choose New Flat Connection.

SSIS  - Connection Manager

In dialog box Flat File Connection Manager Editor you should to choose file source for create File that you need, after you choose file then you click Columns to read the header of file, here is the example file and format is .csv

SSIS  - Connection Manager

After you create database connection and file flat connection, now you can add Data Flow Task on the left pane to your Package by double click Data Flow Task or click and drag

SSIS  - Connection Manager

Then double click Data Flow Task from your package and it will be jump to tab Data Flow, in that workplace add OLE DB Source by double click

SSIS  - Connection Manager

After you adding the OLE DB Source, double click OLE DB Source that you have or by right click and then click Edit, you will see dialog box OLE DB Source Editor

SSIS  - Connection Manager

In that dialog on dropdown OLE DB Connection Manager it will be automatically filled with your connection manager, so if you have more than one connection manager in your package you can choose in that dropdown list, and then in Data access mode there are 4 options you should to choose:

  1. Table or view
  2. Table name or view name variable
  3. SQL Command
  4. SQL Command from variable

Data access mode by default it will be set to Table or view but if you want to use direct query command or stored procedure you can choose SQL Command but in this example we will choose Table or view, so when you are choose it you can see list of your table, so click table that you want to generate data to file, and then click OK.

After finish you will see the cross sign red on OLE DB Source will be disappear after you setting your OLE BD.

Next step add Flat File Destination by click and drag it to your workplace, put it under OLE DB Source

SSIS  - Connection Manager

And then click on OLE DB Source there are 2 constraints when you click it, Blue constraint for success and Red constraint for failure, so what you need to do is click and drag Blue constraint to Flat File Destination like above.

After that, double click Flat File Destination and dialog box Flat File Destination Editor will be pop up,

SSIS  - Connection Manager

In that editor Flat File connection manager by default will be set to your File Connection that you created before and there is warning in the below its mean you should mapping column from table to file destination, click Mappings on the right side and then pairing every column that you have.

SSIS  - Connection Manager

Click Ok to finish and after you setting all of them, run your project by clicking Start button. When you are running your project and you get green checklist in your package, its mean your project running well but if you get red sign, its mean your project is getting error.

Then check and open your .csv file and you will see your data from table will be copy to your file.

Stay tuned to get more tutorial, you can check this to see all of flutter tutorials.