• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

SSIS and Handling Importing 2 type flat file

We have 2 sets of CSV flat files (37 columns and 15 columns). From the user perspective, these 2 type of files will treated as 1 file type. Then in the system somehow to need to cater this as seperate events.

So what we have done:
1. Connetion MAnager (flat file), we created 37 columns initially to cater this.
2. Some with Flat File Source as well OLE DB Destination with 37 columns.

What we found that if the data was loaded with 37 columns then this thing is ok BUT if we loaded with 15 columns data then the database fill in with almost 1/3 of this data due the data try to fill in column 16-37. So for instance we knew that from the original data that it has 12000 records but now in the datasbe almost 4000 records and we check the column 16-37 and there is a data in there.

The question are:
1. How to force only upload what ever in the original file is.
2. If there is no way to do this, how to detech the number of columns before proceeding this step.

I am appreciated your comment.

  • 2
1 Solution
>1. How to force only upload what ever in the original file is.
 You have to have 2 connection managers. One for the 15 column csv file and one for the 37 column csv file. You must have 2, no way around it. The metadata for the file is not dynamic. Once you determine the number of columns in the file then you will choose which of your 2 Data Flow tasks to execute.
>2. If there is no way to do this, how to detech the number of columns before proceeding this step.
Yes, you need to open the file and read in the data into a string variable. Read the first row of the string and count the commas. 14 commas is a 15 column file.  A LF or CRLF will indicate the end of your row. This can all be done in a Script Task.
The following is an instruction on how to load the file into a variable.

This script will open the file, based on the filename in the CSVFILE variable, read th first row, count the number of commas and give you a message box to validate.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.
    Public Sub Main()
        Dim errorInfo As String = ""
        Dim Contents As String = ""
        Contents = GetFileContents(CStr(Dts.Variables("CSVFILE").Value), errorInfo)
        If errorInfo.Length > 0 Then
            MsgBox(errorInfo, MsgBoxStyle.Critical, "Error")
            Dts.TaskResult = Dts.Results.Failure
            Dts.Variables("FILE_CONTENTS").Value = Contents
            MsgBox(Contents, MsgBoxStyle.OkOnly, "First Row of Data")
            'MsgBox(Dts.Variables("FILE_CONTENTS").Value, MsgBoxStyle.OkOnly, "Variable Contents")
            'count the commas for column count
            Dim ColCount As Int32
            ColCount = 0
            Dim nDex As Integer
            Dim nNxt As Integer
            nDex = Contents.IndexOf(",", nDex)
            While nDex > 1
                ColCount = ColCount + 1
                nDex = Contents.IndexOf(",", nDex + 1)
            End While
            MsgBox(ColCount.ToString, MsgBoxStyle.OkOnly, "Columns Found")
            Dts.TaskResult = Dts.Results.Success
        End If
    End Sub
    Public Function GetFileContents(ByVal filePath As String, Optional ByVal ErrorInfo As String = "") As String
        Dim strContents As String
        Dim objReader As IO.StreamReader
            objReader = New IO.StreamReader(filePath)
            strContents = objReader.ReadLine
            Return strContents
        Catch Ex As Exception
            ErrorInfo = Ex.Message
        End Try
    End Function
End Class

Open in new window


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now