SQL Server SSIS package: Connection Manager

SSIS: New Connection from Data source

New Connection from Data source

For making connection to Data source, we need to create one data source.

Data source is nothing but a connection reference that is created outside a package. It can be created in the same manner as we did for OleDb connection.

The advantage of using data source connection is that it can be shared between many packages in a project. Therefore the synchronization becomes easier.

For example, if we have to change the server name, then we don't need to change it in all the connection in the packages that reference the same data source, instead we just need to change the server name in the data source, and it will reflect in all other connections in packages, which reference to the same data source.

For this, go to the solution explorer and right click on Data source and select New Data Source. It will pop up a wizard.

In the Data Source Wizard, Click on new, and you will get the same window, which was used during OleDb connection.

After creating the Data Source, Go to the connection manager and select ‘New Connection from data source’, which will pop up one window as below with available data sources. You just need to select the data source, and your connection is created.

Now if we change the database or server name or any other property in Data Source, then it will also reflect in all the connection referencing the same data source. J

For all other connection manager click on below links
OleDb and ADO.Net connection
Flat File Connection
File Connection
New Connection

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

SSIS: File Connection

File Connection

File connection is used to make connection to any type of file, be it excel, flat, image, any type. Even we can use it to make connection to a folder instead of file. If the file or folder doesn’t exist then new folder or file can also be created.

File can also be created dynamically by providing expression in the properties of file connection manager.

Use in real time scenario:

Sometime it happens, we need to run our package on a scheduled basis say daily. Also, we need to save the result of the package in some folder to do analysis on the results generated.

The solution is to create the file dynamically, that is the file name will be generated on the basis of some system or user variable. It will be different each day, if we also use date in the file name. This can be done by setting the expression in file connection properties as shown below.

For creating the connection, go to the Connection Manager and select File Connection.

Go to the properties of File Connection Manager, and then go to expression. It will open a Property Expression Editor, where we can set property like connection string etc.

For all other connection click on below links,
OleDB and ADO.Net Connection
Flat File Connection
Connection from Data source
New Connection

SSIS : Quick view on Flat File Connection

Quick view on Flat File Connection

Go to the Connection Manager, and select Flat File Connection. Then follow the steps as mentioned below:

Provide File path in file name.
Select format ‘delimited’, if the rows in the file are delimited by comma, space, tab etc.Fixed width: if the Columns have a fixed width.Ragged right: Ragged right files are files in which every column has a fixed width, except for the last column. It is delimited by the row delimiter.
Delimiter is nothing but separator character, which separated, rows, columns.
Select the Header row delimiter from the drop-down say if the header in my file is delimited by comma, then select comma.
In Columns tab, Column and row delimiter can also be set. Here row delimiter is set to CR [LF], which is ‘enter’ and column delimiter is comma.
In Advanced tab, we can see the data type length etc assigned to the column, also we can change the data type and length of the column to prevent truncation of data or excess column width.
By default data type is string and minimum length 50.
By clicking on ‘Suggest Column Types’, it will assign column width to the columns, which will be maximum length of the text in that column, Also we can change the data type here only as per the destination column requirement. It will reduce overhead of Data conversion thing, if we do it here only.

For all other connection click on below links,
OleDB and ADO.Net Connection
File Connection
Connection from Data source
New Connection

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.