Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

Previously, I have posted some SSIS solutions which would handle dynamic column mapping in data loading. This time, I was wondering how to handle dynamic column mapping when the source is an excel file. I was quick to realize that it would be entirely a C# solution and the program should be able to handle … More Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

Avoid Concurrency problem while running SQL Server Jobs

Hello everyone, This time my problem was to delay the start of a SQL server job if another job is already running. Obviously, the reason for this is that the jobs use the same database resources. The sysjobhistory table in MSDB database has the job ids and their status stored. First, I selected the job … More Avoid Concurrency problem while running SQL Server Jobs

Update another table using Data Macro in MS Access 2010

Recently, I had to find a way to write a trigger in MS Access 2007. My aim was to update a field in one table (let’s say Project Table) based on Insert/Update/Delete operation on another table (Let’s say Investors table). Quickly, I realized that MS Access 2007 does not offer much when it comes to … More Update another table using Data Macro in MS Access 2010

SQL Server Agent needs to Log on As Local System for using Oracle Oledb Provider in SSIS Job

This problem was one of the most terrible one in my short SSIS career. I was using a script task which uses Oracle Oledb provider to connect to an oracle server. After the package was complete, I created a SQL server job and obviously the job’s step was configured to run from SQL Server Agent … More SQL Server Agent needs to Log on As Local System for using Oracle Oledb Provider in SSIS Job

the connection string format is not valid . it must consist of one or more components of the form….error

Back again with another SSIS SQL Server based configuration problem. I was using SQL server based configuration and the SSIS configuration wizard created one table with the default field lengths. The table is like this: CREATE TABLE [dbo].[SSIS Configurations](     [ConfigurationFilter] [nvarchar](255) NOT NULL,     [ConfiguredValue] [nvarchar](255) NULL,     [PackagePath] [nvarchar](255) NOT NULL,     [ConfiguredValueType] [nvarchar](20) NOT NULL ) … More the connection string format is not valid . it must consist of one or more components of the form….error

Password must be included in connection string for SQL server based configuration in SSIS

Recently, I have been working with SSIS configurations a lot. At the beginning, it seemed not much difference between XML configuration and SQL server configuration to me. Very quickly, I was proved wrong. In the configuration wizard, we can choose several properties of a connection; i.e. Connection String, User Name, Password, Server Name, Initial Catalog … More Password must be included in connection string for SQL server based configuration in SSIS

Dynamic Column Mapping in SSIS : Part 2 (using dts variable)

Previously I have discussed the dynamic column mapping using script component. Now, I am going to mention one more way of dynamic column mapping in SSIS. Let’s say, our source table is Table_1: ID    JAN    FEB    MAR 1    100    200    300 2    400    500    600 3    700    800    900 We want to map its columns dynamically in data flow task. First, u need to create a DTS … More Dynamic Column Mapping in SSIS : Part 2 (using dts variable)