Copy SQL Server Data to PostgreSQL vice versa using SSIS( SQL Server Integration services) using Free tool Devart Express edition

We can connect PostgresSQL using the Devart Free SQL Edition using SSIS tools(Visual Studio 2019) tools. 

Open Visual Studio and go to Extensions --> Manage Extensions

Its a Free tool to connect using PostgreSQL with basic limited features. But it support creating destination table if not exists.

It will redirect ot their link

dotConnect ADO.NET Data Provider for PostgreSQL Express - Visual Studio Marketplace

Download dotConnect for PostgreSQL (devart.com)

Download the Free version of dotConnect for PostgrSQL 7.20 Express

After installation, you can restart visual studio and open SSIS projects.

Select new connection. Select the dotConnect for PostgreSQL

Provide all the details and select allow saving password so that the password can be used while executing the package.

Save the connection. use the Ado.net Destination.

Map the columns and save. We can use click if we need to create a destination table.

run the SSIS Package. Package running fine.

Copy SQL Server Data to PostgreSQL vice versa using SSIS( SQL Server Integration services)

 SQL Server Data tools (Integration services) tools allows the third party Intgeration with SQL Server Integration Project which comes with Visual studion. 

SSIS does not have buit-in Integration with PostgreSQL but allows integration through third party Applications.

Go to Visual studion 2019. In Extensions menu Manage Extensions.

Search with Postgre. 

The first one Npgsql PostgreSQL Integration is free but does not support using SSIS. We can use this only for server explorer which can be intrgrated using Visual Studio .Net.

There are many vendors which provides 30 days trial period. One of them is SSIS PostgreSQL Source. when click on download it will redirect to the page

Download SSIS PowerPack - 30 Day FREE Trial Includes: | ZappySys

After registering you can download and use trial version for a month. This application provides many connector source systems not only PsotgreSQL.

These are the different connector available after installing the software. Postgre is also there, I have highlighted the same.

But I feel it do not have any option to create a table if does not exist. Table must exist in SQL server. 

Their connections are working fine as exptected. I ran one SSIS task to load one sample table it working fine.

Package execution was sucessfully after mapping the columns. But there should have been an option to create destination table based on source table columns this is only one drawback i have seen in this product.

Other Vendor who is supporting free. But they also support basic featres. That will redirect to the page

Download dotConnect for PostgreSQL (devart.com)

Highlighted one is free. This option also can be tried.

How is install SQL Server Integration Services(SSIS) or SSDT tools using Visual Studio 2019

 

Microsoft stopped proving separate install for SSDT tools. It is now part of Visual studio 2019.

It should be installed using visual studion 2019 package.

Once you have installed visual studio. Using the Visual studio installer SQL server integration services tools can be installed.

Launch Visual Studio installer.

Click on modify and Go to Workloads

 

Scroll down and select Data Storage and processsing and ensure that you have selected the SQL server Integration services project

select any of the optin install while dowloading or download and install

 

Once after installation if you open installer again in indivdual components sections you can it istalled.

Same can be seen in the installation details

Rename the files in a folder using the SSIS(SQL Server Integration Services) or SSDT Tools(Visual Studio 2019)

Renaming the files in a folder using SSIS is very easy. We need just two tasks.

1) ForEachLoop Container

2)File System task

These both are available in Control-Flow Task.

First Place For Each Loop Container and then place File System Task. 

Second Create a variables as shown below.

Filename : This variable is used for Dynamically getting the Filename in SSIS For Each Loop container. We need to set some default name (abc.txt) so that it will create failure in build.

FilePath : This variable is static. You can hardcode the variable path. Or You can create connection and get the File path. Here I created variable and Kept Some Local Path.

SourceFileName : This variable is Dynamic. Use the expression to use the Filename variable so that when ever you get value from For each Loop it will set to SourceFileName so that you will not get error. 

You cannot use the same variable for setting the filename and getting the filename.

 Error at File System Task: Failed to lock variable "G:\SSISFiles\IMG_ 20190203_165954.jpg" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

DestFileName : This variable is used for Dynamically setting the filename. We can write expression how to rename the files.

Suppose I have image files from different source like whatupp, mails and vedios but i need to sort all based on the timestamp but the inital name was different. So I can use this replace the name of file so that all will be in sorted manner. 

After setting the variables we need to set the For Loop container values to pickup dynamically 

in folder.

Double click on For Each Loop and Go to the Collection tab. Select Enumerator as For Each File Enumerator.

In Expressions set Directory for the File Path Variabe

FileNameRetieval will get Filename and sets to Filename variable.

In Variable Mappings, Use the the same sequnce to assign the variables using the same order

Index starts from Zero.

Click Ok. Edit the File System task inside the For each loop container.

Set OverwriteDestination to False so that it will not overwrite the files if it matches.

For Destination and Source connection set the Paramaeters IsDestinationPathVarible and 

IsSourcePathVariable to True. By default it will be false. Here we have parameterised to use dynamically so we need to set to "True"

SourceVariable : This variable is used for Dynamically getting the Filename in SSIS For Each Loop container. We have wrtitten this expresssion in variable definition. So it will be same and Filename and can be accessed across SSIS Package.

DestinationVariable : This variable is Dynamic. We have set the same to replace "IMG"

name to "VID".

Also do not use the expression to use variable which has been set in For Each Loop Container to set in File System Task.

Error at File System Task: Failed to lock variable "for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Ex:

----------

Before running the Task. You can see there are different file types but I want to keep same name format. I can do that using this simple task.

After Executing the SSIS Task.

We can clearly see that after running the task file names got changed.

Working with For Loop Container in SSIS

Its is easy to use For Loop Container in SSIS. For Loop Container mainly used for execution of certain task for specified number of times that match the condition.We can use the for loop for updating big table and split updated in batches.

Here I have Shown a simple For Loop Task . Its prints from 0-9.

We declared three variables  as shown below. 

Start for Initial position .

Inc for Increment

End for condition satisfying until.

Double Click on ForLoop Container to Configure the For Loop Properties.

Set the properties as shown below.

In order to check whether the For Loop is Working Fine or Not I have used the Script Task to show the message box pop up. Script task is very useful to implement the custom functionality as well as useful for debugging.

Set the ReadOnlyVariables for the variable Start  to show that variables getting incremented by the for each loop logic.

 Click on Edit Script. 

Write the below code in main. 

MessageBox.Show(Dts.Variables[0].Value.ToString()

Save the script and Close the Editor. Build and Run the SSIS Package. Message Box prints from 0 to 9.

Loop through Excel Files using SSIS ForEachLoop Task

Load the Multiple Excel files data in a folder to SQL Server using SSIS ForEachLoop Task:

Using SSIS ForEach Loop Task it is easy to Copy multiple excel in SQL Server.

Suppose you need to load multiple excel files from a folder with same structure in all excel files into SQL Server. Let Say excel contain daily data.

To do this we require Two Tasks. One Data flow task and One For EachLoop Container.

The we have to create an Excel Connection and SQL Server Database Connection for source and destination.

We need to create the below variables for storing the File Path and File name. Actually we require only 2 variables. One is for storing the File Directory and another is for storing the 

File Name that will pick up by the ForEach Loop Container.

In Excel Connection Manager Properties.  Excel Connection creation can be implemented by using the 

Go  to Expression Field.

Double Click the Expression . Select ExcelFilePath Property and Add the expression
as mentioned below.

Set  ServerName Propery to Some hard coded excel file which exist so that it will not fail while running.

Other wise it will throw the below error.

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[Connection manager "Excel Connection Manager 1"] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft JET Database Engine"  Hresult: 0x80004005  Description: "Failure creating file.".

Also use the both servername and ExcelFilePath properties instead of ConnectionString property .Other wise you will get the below error. It always better to use ExcelFilePath property for the excel connection.

[Connection manager "Excel Connection Manager"] Error: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error: The result of the expression "@[User::FilePath]+  "\\"  + @[User::FileName]" on property "\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

 Once the Excel Connection Manager Configured then configure Foreach Loop.

 In the ForEach Loop Editor . Select Collection tab.

Go to Expressions. Set the Directory Property to FilePath and FileNameRetrieval to FileName.

The Check box Traverse Sub folders can be checked if need to check sub folders in directory.
Then Go to Variable Mappings . Map the variables to index.

The Index of FilePath should be zero and FileNamr should be one. It should follow the same order as mentioned in the Expression.

Other wise you may get the below error while executing the package.

Logging in SSIS (SQL Server Integration Services)

How to Log Errors/Warning in SSIS

Its easy to log execution details, errors,warning in SSIS. In order to log execution events.

Go to SSIS Menu

Click on Logging as shown in the above.

There are multiple options to log the events in SSIS. Different Options are

1) SSIS Log Provider for SQL Server Profiler : Log Events will be saved in trace files. The Log can be opened using SQL Server profiler. Later it can be saved  into SQL Server database using the method fn_trace_gettable

2) SSIS Log Provider for XML files : Log Events will be saved in XML Files. This XML file we can save into SQL Server later using the Rowset functions (OPENXML).

3) SSIS Log provider for SQL Server : Log Events will be saved in SQL Server. SQL Server creates an internal table.

The log details will be stored in sysssislog table. The message column stores the error message.

4) Sqlserver log provider for Windows Event Log : Log Events will be saved in Windows Event Log.

5)Sqlserver log provider for Text files : Log events will be saved in text files.

There are two tabs to configure the logs in ssis.

1) Providers and Logs :

In this we can select the log providers to save the log events include errors,warnings.

2) Details : In this tab we can select list of events that required for logging.