SSIS and Handling Importing 2 type flat file

Posted on 2008-11-13
Last Modified: 2013-11-10
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.

Question by:dewacorp_alliances
    LVL 17

    Expert Comment

    >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.

    LVL 17

    Accepted Solution

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now