Read source file from and XML file

Question

I have a process that is receiving files that come in pairs. One is a ctr file and the other is the data file. The ctr and data file will always have the same name except different extensions. E.g. dfghj.ctl and dfghj.dat

The problem I have is that I receive multiple of these pairs at the same time. The only way to tell which is which is to look into the ctl file. This tells me if the the .dat file related to it is either the report.jsonl file or the delta.jsonl file.

An example of a .ctl is:

<DTSControl>
<Version>1.0</Version>
<AddressType>DTS</AddressType>
<MessageType>Data</MessageType>
<From_DTS>x26OT075</From_DTS>
<To_DTS>x26OT075</To_DTS>
<Subject>ECDS Submission</Subject>
<LocalId>TEST-delta.jsonl</LocalId>
<WorkflowId>SUS_CDS</WorkflowId>
<Encrypted>N</Encrypted>
<Compress>Y</Compress>
</DTSControl>

The bit I need to get to is the LocalId which tells me what type of file the .dat is and therefore which table I need I need to load it to. Once I know which file is which, I then need to pass the associated .dat file name to a variable to populate the source connection.

The only thing I can think of is a script task to load a ctl file, isolate the LocalId and if it is like *-report.jsonl then strip the last three characters of the file and replace ctl with dat and pass that string to the variable but I've no idea how to get to LocalId as I don't work with c#.

Is anyone able to give a pointer as to how to get there? It may not always be the eighth line so I would prefer to tell it to go LocalID which won't change.

Thanks in advance

score 1 · Answer 1 · answered Dec 08 '17 at 15:35

Try xml linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;


namespace ConsoleApplication16
{
    class Program
    {


        static void Main(string[] args)
        {
            string xml =
               "<DTSControl>" +
                    "<Version>1.0</Version>" +
                    "<AddressType>DTS</AddressType>" +
                    "<MessageType>Data</MessageType>" +
                    "<From_DTS>x26OT075</From_DTS>" +
                    "<To_DTS>x26OT075</To_DTS>" +
                    "<Subject>ECDS Submission</Subject>" +
                    "<LocalId>TEST-delta.jsonl</LocalId>" +
                    "<WorkflowId>SUS_CDS</WorkflowId>" +
                    "<Encrypted>N</Encrypted>" +
                    "<Compress>Y</Compress>" +
               "</DTSControl>";

            XDocument doc = XDocument.Parse(xml);

            string localId = (string)doc.Descendants("LocalId").FirstOrDefault();
        }
    }

}

score 1 · Answer 2 · answered Dec 08 '17 at 17:03

So far both of these solutions helped you identify LocalID. Great.

But I think you are asking a bigger picture. How does your package look.

Foreach loop through files with your mask of *.ctl
Store the file name in a variable
Add a script task and use whatever method you want to find local ID and store in a variable
You need to have a data flow for each of the possible local ids.
Set precedence constraints to those data flows based on local id
Your target file will always be based on this formula and you should use an expression for your connections: Left([Variable from Step 2],len([Var from 2])-3)+"dat"

score 0 · Answer 3 · answered Dec 08 '17 at 15:35

Here a solution using Regex:

//var input=File.ReadAllText("someFile.ctl");
var input=@"<DTSControl>
<Version>1.0</Version>
<AddressType>DTS</AddressType>
<MessageType>Data</MessageType>
<From_DTS>x26OT075</From_DTS>
<To_DTS>x26OT075</To_DTS>
<Subject>ECDS Submission</Subject>
<LocalId>TEST-delta.jsonl</LocalId>
<WorkflowId>SUS_CDS</WorkflowId>
<Encrypted>N</Encrypted>
<Compress>Y</Compress>
</DTSControl>";
Regex reg = new Regex(@".*<LocalId>(.*?)</LocalId>.*");
// Check, if it exists in the string
if(reg.IsMatch(input)){
    // Get all the Matches (here: 1)
    var mtch=reg.Matches(input);
    // collect the good stuff from the capture group
    Console.WriteLine(mtch[0].Groups[1].Value);
}

Read source file from and XML file

3 Answers3