Databases
StackOverflow2013
Postsdbo
POConditional Data Flow Based on Source CSV Filename
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POConditional Data Flow Based on Source CSV Filename
    primarykey
    data
    text
    <p>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.</p> <p>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 <a href="http://dichotic.wordpress.com/2006/11/01/ssis-test-for-data-files-existence/" rel="nofollow noreferrer">here</a> 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:</p> <pre><code>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 </code></pre> <p>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 <code>User::fileName</code> 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.</p> <p>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 <code>User::fileName</code> as the input and get then test the boolean values of the other 3 <code>User::</code> variables to make the 3 branches.</p> <p>My current thinking is this: Inside a single For Each Loop there is this Data Flow Task:</p> <pre><code> Scripting Task / | \ (Type1) (Type2) (Type3) | | | CSV_Files1 CSV_Files2 CSV_Files3 | | | OLEDB_Dest1 OLEDB_Dest2 OLEDB_Dest3 </code></pre> <p>Where the CSV_Files1-3 use a unique SourceConnectionFlat file to read/map the columns to the OLEDB_Dest tables.</p> <p>EDITS: <img src="https://i.stack.imgur.com/56gfo.png" alt="Design Script Error"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USwergeld
    1. USwergeld
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POConditional Data Flow Based on Source CSV Filename
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload