Click here to Skip to main content
15,896,118 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi all,
Please kindly suggest, I want to import data from flat file, which provide different column width & different field count in the same file.

Base on the first column .
eg. If first column is 'N', then 2nd column width is (8), 3rd column width is (24), fourth column width (9) ... ect...
If first column is 'P', then 2nd column width is (36), 3rd column width is (9), fourth column width (9) ... etc...
If first column is 'D', then 2nd column width is (14), 3rd column width is (18), fourth column width (7) ... etc...

Example data
*************
VB
N 001     John                    19750805 M
P Engineer                            20000904 20050905
P Manager                             20060505 20100506
N 002     Susan                   19800607 F
P Programmer                          20020607 20050604
D Ayiuapid      sdflkle           sfsdfsd

Any idea for this with using SSIS?

Thanks and best regards...
Posted

1 solution

Write custom source with Script Component: I can give you a sample.
Add connection to your text/csv file and name it as MyFlatFile
So in your script component, Add the following things:
1. Make the script compenent as source when drop it on data flow tab
2. Click "Add" in connection managers(inside script component) and select MyFlatFile conn from drop down
4. Go to Input and Output tab.
5. Rename Output0 to My
6. Define all the columns you need by clicking Add Column, you may set Unicode string [DT_WSTR] for string columns and specify max length as 100, just common figure 100
7. Edit script and replace all code with the following(do remember to make appropriate changes on the columns you defined on step 6):

C#
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private StreamReader textReader;
    private string SourceFeed;

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.MyFlatFile;
        SourceFeed = (string)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(SourceFeed);
    }

    public override void CreateNewOutputRows()
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = "|".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
          columns = nextLine.Split(delimiters);
          if(MyBuffer.Length > 3)
          {  
           MyBuffer.AddRow();  
           MyBuffer.Type = columns[0].Trim();
                if (columns[0]=="N")
                {
                    MyBuffer.Designation = columns[1].Trim();
                    MyBuffer.Date = columns[2].Trim();
                    MyBuffer.Gender = columns[3].Trim();
                }
                if (columns[0]=="P")
                {
                    MyBuffer.Name = columns[1].Trim();
                    MyBuffer.CreateDate = columns[2].Trim();
                    MyBuffer.ModifiedDate = columns[3].Trim();
                }
                if (columns[0]=="D")
                {
                    MyBuffer.Name = columns[1].Trim();
                    MyBuffer.SecondField = columns[2].Trim();
                    MyBuffer.ThirdField = columns[3].Trim();
                }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        base.PostExecute();
        textReader.Close();

    }


}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900