Conditional Data Flow Based on Source CSV Filename

Question

I have a series of data that is produced by state and information type. For each state I have, for example, 3 files that have an identifier in the file name to tell me which information group it is for. The data in these files consists of rows of data that have varying lengths depending on the file type. Type 1 always has 245 comma separated values, Type 2 has 215, Type 3 has 300.

I have 3 separate SSIS 2005 packages set up to import this data but I was wondering if there is a way to do this in one package with either a Conditional Split or a Scripting Task. I have tried the Conditional Split but it looks to me like it is expecting to have to check a row value. I then looked at the code here that uses a Scripting Task. I cannot get this code to compile as it complains about "DTS not being defined." This is probably one of those cases where the author assumes I have knowledge in some, for him, basic SSIS idea - and that I do not have that knowledge. I have modified the code there to be:

Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    ' Created: Zack Bethem – AmberLeaf
    Public Sub Main()
        Dim fileLoc, fileName As String
        If Dts.Variables.Contains("User::fileName") = True Then
            fileName = CStr(Dts.Variables.Item("User::fileName").Value)
            If fileName.Contains("0074000") Then
                Dts.Variables.Item("User::SexByInd").Value = True
            ElseIf fileName.Contains("0072000") Then
                Dts.Variables.Item("User::SexByOcc").Value = True
            ElseIf fileName.Contains("0022000") Then
                Dts.Variables.Item("User::Migration").Value = True
            End If
            Dts.TaskResult = Dts.Results.Success
        Else
            Dts.TaskResult = Dts.Results.Failure
        End If
    End Sub
End Class

As you can see from the code: I am looping over the files in a directory using a For Each loop. I am assigning the file name for each file to User::fileName and then checking if the file name contains one of 3 string elements. If it contains Type 1 then it goes to a different branch than Type2/Type3, etc.

Since this code does not compile I am cannot check to see if the rest of his example will work. I am not sure what kind of Scripting Task he is using either since I was presented with 3 options but all involved some kind of manipulation of a row or row element of data from as the Input to the Scripting Task. What I want to do is pass the User::fileName as the input and get then test the boolean values of the other 3 User:: variables to make the 3 branches.

My current thinking is this: Inside a single For Each Loop there is this Data Flow Task:

           Scripting Task
      /         |           \
   (Type1)   (Type2)       (Type3)
    |            |            |
 CSV_Files1  CSV_Files2    CSV_Files3
    |            |            |
OLEDB_Dest1  OLEDB_Dest2   OLEDB_Dest3

Where the CSV_Files1-3 use a unique SourceConnectionFlat file to read/map the columns to the OLEDB_Dest tables.

EDITS: Design Script Error

grapefruitmoon · Accepted Answer · 2011-02-24 18:21:11Z

2

In a Script task in the Control Flow, you can check the Filename (in a variable here) and set a variable called Type...

    If Dts.Variables("User::FilePath").Value.ToString.Contains("cl") Then
        Dts.Variables("User::Type").Value = 1
    Else
        Dts.Variables("User::Type").Value = 2
    End If

    Dts.TaskResult = Dts.Results.Success

Then use precedent constraints (double click on the constraint line) to check for Success of the Script Task, and the value of the Type variable, as shown here.

enter image description here

The flow would look similar to this, with each constraint checking a different value of Type...

enter image description here

You could drop this into a For Each task to loop through your folder and set the FilePath variable.

edited Feb 24, 2011 at 18:21

answered Feb 24, 2011 at 18:16

grapefruitmoon

3,0281 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wergeld Over a year ago

Okay, that is a variation on a theme here and do-able. The problem is that the "DTS.Variable" code does not "work". The Design Script window states that: Name "DTS" not declared". Because this fails to compile I cannot draw the Constraint Lines. Also, because no Input or Output columns are specified it will not let me "draw the lines".

grapefruitmoon Over a year ago

Actually, it looks like you're doing this in the Script Component as part of a Data Flow, rather than a Script Task in the Control Flow...is that correct? The code above needs to be in the Script Task.

wergeld Over a year ago

Yes, this the Script Task is part of the Data Flow. The code is in the Script Task. Let me move the Script Task to the Data Flow and see what happens.

wergeld Over a year ago

That was it. Had the Script Task in the wrong location. I did a quick test and it worked as expected. Thanks a bunch!

wergeld Over a year ago

Actually, somehow I missed this - but this still fails but with a new error: Error: 0xC001F009 at ACS_ALL_Import: The type of the value being assigned to variable "User::Type" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::Type" cannot be applied. I have set the User::Type variable to Int32. I have even tried Boolean, String, etc (changing the assigned value each time) with no change.

Collectives™ on Stack Overflow

Conditional Data Flow Based on Source CSV Filename

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related