CODEWITHSUNDEEP
sql-servervb.netlinqssisscript-task
Dasso
Dasso

Reputation: 141

Reading data from LINQ query

I am new to LINQ query and writing a SSIS script task to read data from two data tables. I have created the following query, where I want to output to be sent by an email as a table. The body of the email will be the output.

I am able to see the result. But dont know how to use this data (New to linq). Here is my code:-

    Dim Filename As String
    Dim Filepath As String
    Dim i As Integer


    Filename = "TM_xxx_DAILY_*" + Dts.Variables("User::VLoaddt").Value.ToString + "_*.txt"


    Filepath = Dts.Variables("User::vSrcFolder").Value.ToString

    Dim di As DirectoryInfo = New DirectoryInfo(Filepath)
    Dim fi As FileInfo() = di.GetFiles(Filename)

    Dim DestTab As DataTable
    DestTab = New DataTable("DestinationTable")

    Dim column As DataColumn = New DataColumn("Dest")
    column.DataType = System.Type.GetType("System.String")


    DestTab.Columns.Add(column)

    DestTab.Rows.Add("TM_xxx_ONLINE")
    DestTab.Rows.Add("TM_xxx_RETAIL")
    DestTab.Rows.Add("TM_xxx_TELESALES")
    DestTab.Rows.Add("TM_xxx_DAILY_DEVICE")

    Dim SrcTab As DataTable
    SrcTab = New DataTable("SourceTable")

    Dim column1 As DataColumn = New DataColumn("Source")
    column1.DataType = System.Type.GetType("System.String")

    Dim column2 As DataColumn = New DataColumn("FileExists")
    column2.DataType = System.Type.GetType("System.String")

    SrcTab.Columns.Add(column1)
    SrcTab.Columns.Add(column2)


    For i = 0 To fi.Length - 1
        SrcTab.Rows.Add(Left(fi.GetValue(i).ToString, Len(fi.GetValue(i).ToString) - 20), "Exists")

    Next

    Dim query =
    From a In DestTab
    Group Join b In SrcTab
        On a.Field(Of String)("dest") Equals b.Field(Of String)("Source")
        Into Group
    Let b = Group.FirstOrDefault
    Select dest = a.Field(Of String)("dest"), FileExists = If(b Is Nothing, "Missing", b.Field(Of String)("FileExists"))

The biggest challenge and I am not able to understand how to use the variable "query" in "Dim query". While examples in the net and able to use it as a datarow, copytodatatable and other. I can only see tostring,equals and things like that.

My objective is to read files in a folder join it with "Destinationtable" and find the missing ones.

The codes written below are the one right after the select statement

Error Screenshots enter image description here enter image description here

Upvotes: 1

Views: 684

Answers (1)

Hadi
Hadi

Reputation: 37313

I think you should use 

For each line in query.ToList()

    Dim drRow as DataRow
    drRow = MT.NewRow
    drRow("Filename") = line.Item(0)
    MT.Rows.Add(drRow)

Next

Instead of

For each line in query
    MT.Rows.Add(query)
Next

Upvotes: 1

Related Questions