Creating a Connection Manager
Click Windows START ->All Programs -> Microsoft SQL Server 2012 – > SQL Server Data Tools for Visual Studio (This opens Visual Studio)
From Visual Studio Select File -> New -> Project and you will be on the following screen
Screen Shot of BIDS New Project for SSIS
Click <OK> will take you to the following screen:
On the main screen you will notice Package.dtsx (name of the package)
Under the package there are 5 tabs
- Control Flow
- Data Flow
- Event Handlers
- Package Explorer
Control Flow: A control flow defines a workflow of tasks to be executed, often a particular order (assuming your included precedence constraints). The looping example is a good example of a control-flow requirement, but you can also execute standalone SQL Scripts, call into COM interfaces, execute .NET components, or send an email. The control flow task itself may not actually have anything whatsoever to do with a database or a file.
Data Flow: A data flow defines a flow of data from a source to a destination. You do not start on one data flow task and move to the next. Data flows between your selected entities (sources, transformations, destinations).
parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Right Click and select New OLEDB Connection and it will bring the following screen.
Connections: 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.
Enter LocalHost or Any Server Name in SERVER NAME
Select AdventureWorks and Click OK
Connection Manager is created.