CODEWITHSUNDEEP
sql-serverssisetlflat-fileflatfilesource
Nilesh
Nilesh

Reputation: 21

How to import Flat-file with no column header using SSIS?

I want to import the flat file using SSIS. My flat file has 50 columns but no column header. So it shows like Column 0, Column 1, and so on. I don't want to manually assign column name using the advanced editor. Is there a way to dynamically assign 50 column names. So that Column 0 = Client , Column 1 = Date and so on.

Upvotes: 2

Views: 1930

Answers (2)

Hadi
Hadi

Reputation: 37313

In addition to @billinkc answer, there are other options if you are familiar with C#:

(a) Using SQL Server Client SDK assemblies

This is the official traditional way to create ssis packages programmatically, there are many link on the internet that you can refer to such as:

(b) Using EzApi – Alternative package creation API

EzAPI is a .NET library written in C# by Evgeny Koblov one of the testers on the SSIS team to abstracts away a lot of the cumbersome low-level coding needed to create SSIS packages XML directly in a programming language

There are many link on the internet that you can refer to such as:

(c) Using SchemaMapper class library

Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table with a different schema definition using schema mapping approach. check it out at:

You can follow this Wiki page for a step-by-step guide:

You can use this library to read from flat file and import into SQL with a few lines of code*.

Upvotes: 0

billinkc
billinkc

Reputation: 61201

Option 1 would be to install the BI Developer Extensions and use the "Create Fixed Width Columns" tool https://bideveloperextensions.github.io/features/CreateFixedWidthColumns/

Option 2 Define your flat file with Biml and generate your package (and connection manager) there. I have plenty of examples of doing this in Biml

Upvotes: 1

Related Questions