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

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)

Dynamic Column Mapping in SSIS : Part 1

SSIS Data Flow Task is strictly meta data bound. Input table’s columns are mapped at design time. Let’s say u have a table (Table_1) where column names (JAN,FEB,MAR) are dynamically changed and u don’t have any control over it. SELECT * FROM dbo.Table_1 returns ID¬†¬†¬†¬†JAN¬†¬†¬†¬†FEB¬†¬†¬†¬†MAR 1¬†¬†¬†¬†100¬†¬†¬†¬†200¬†¬†¬†¬†300 2¬†¬†¬†¬†400¬†¬†¬†¬†500¬†¬†¬†¬†600 3¬†¬†¬†¬†700¬†¬†¬†¬†800¬†¬†¬†¬†900 In the next month, it becomes ID¬†¬†¬†¬†APR¬†¬†¬†¬†MAY¬†¬†¬†¬†JUN … More Dynamic Column Mapping in SSIS : Part 1

Logical OR Condition in EvalExpression of a For Loop Container

If you double click on the For Loop Container of SSIS, a new window will open where you can set up the condition based on which the loop will continue. There is a field called EvalExpression where you can set an expression and if this expression holds true, the loop will continue. I was just … More Logical OR Condition in EvalExpression of a For Loop Container