HI.. I ahve a created a System.object varaiable with full row set result type, and store these values select ppl_code,ematrix_family from eprw_pplcode -- Using Exectue sql task. Now i want call this variable and store in an array in scrip task, so i can use these values in my script. Please help me.

Use the below code : and make sure you include Oledb library as Imports System.Data.OleDb Dim oleDA As New OleDbDataAdapter() Dim dt As New DataTable() Dim col As DataColumn = Nothing Dim row As DataRow = Nothing Dim strMsg As String = Nothing oleDA.Fill(dt, Dts.Variables("var").Value) For Each row_ As DataRow In dt.Rows row = row_ For Each col_ As DataColumn In dt.Columns col = col_ strMsg = strMsg & col.ColumnName & ": " & row(col.Ordinal).ToString() & Environment.NewLine Next MessageBox.Show(strMsg) strMsg = "" Next

Hi .. Thanks Harshvai but when i use this it is giving error at Dim dt As New DataTable() i am using Foreach Loop i am storing the record set in a variable . I want to use that variable in for each loop and i am using for each file enumerator. I want to use this variable to compare the values coming from files in for each loop. Please help me.

I have corrcted the code above please check now...also let me know what exact error you get

Thank you, It worked.. Now i am trying to insert those values into an array, But the values are not adding . Its repeating can you please have a look. Dim oleDA As New OleDbDataAdapter Dim dt As New DataTable Dim col As DataColumn Dim row As DataRow Dim strMsg As String Dim PPLCodeList As New ArrayList() Dim i As Integer oleDA.Fill(dt, Dts.Variables("PPL_Code").Value) For Each row_ As DataRow In dt.Rows row = row_ For Each col_ As DataColumn In dt.Columns col = col_ strMsg = row(col.Ordinal).ToString() 'MsgBox(strMsg) PPLCodeList.Add(strMsg) i = 0 If i <= PPLCodeList.Count - 1 Then 'MsgBox(PPLCodeList.Count) MsgBox(PPLCodeList.Item(i)) i += 1 End If Next Next End Sub End Class

i cannot understand the If logic you have.....but i tried the below code which shows that the list in the array is correctly populated.... let me know if you still face issues Dim oleDA As New OleDbDataAdapter() Dim dt As New DataTable() Dim col As DataColumn = Nothing Dim row As DataRow = Nothing Dim strMsg As String = Nothing oleDA.Fill(dt, Dts.Variables("var").Value) Dim PPLCodeList As New ArrayList() Dim i As Integer For Each row_ As DataRow In dt.Rows row = row_ For Each col_ As DataColumn In dt.Columns col = col_ strMsg = row(col.Ordinal).ToString() PPLCodeList.Add(strMsg) Next Next For i = 0 To PPLCodeList.Count - 1 MessageBox.Show(PPLCodeList.Item(i).ToString()) Next

Hi Harshvai.. In the below code i am trying to compare the Array values to the value getting from file. I am using this script task in for each loop container. In this code ' 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 System.IO Imports Microsoft.SqlServer.Dts.Runtime Imports System.Windows.Forms Imports System.Xml Imports System.Collections Imports System.Data.OleDb Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper 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 oleDA As New OleDbDataAdapter Dim dt As New DataTable Dim col As DataColumn Dim row As DataRow Dim strMsg As String Dim PPLCodeList As New ArrayList() Dim i As Integer Dim FILE_NAME As String FILE_NAME = Dts.Variables("eprwtesting").Value.ToString() + "\" + Dts.Variables("FileName").Value.ToString() Dim Firstcol As Integer Dim Secondcol As Integer Dim Lastcol As Integer Dim Line As String Dim LineRev As String Dim EmatrixFamilyName As String Dim PPLCode As String oleDA.Fill(dt, Dts.Variables("PPL_Code").Value) For Each row_ As DataRow In dt.Rows row = row_ For Each col_ As DataColumn In dt.Columns col = col_ strMsg = row(col.Ordinal).ToString() 'MsgBox(strMsg) PPLCodeList.Add(strMsg) -- values storing in array Next Next 'For i = 0 To PPLCodeList.Count - 1 ' MsgBox(PPLCodeList(i)) 'Next If System.IO.File.Exists(FILE_NAME) = True Then Dim objReader As New System.IO.StreamReader(FILE_NAME) Line = Line & objReader.ReadLine() & vbNewLine 'MsgBox(Line) LineRev = StrReverse(Line) Firstcol = Line.IndexOf(": ") Secondcol = Line.IndexOf(" DATE:") Lastcol = LineRev.IndexOf("-") EmatrixFamilyName = Line.Substring(Firstcol + 1, Secondcol - (Firstcol + 1)) PPLCode = StrReverse(LineRev.Substring(0, Lastcol)) -- this is the code i am getting from file(ex; i have 4 txt files in a folder) 'MsgBox(EmatrixFamilyName) 'MsgBox(PPLCode) -- Up to here i am getting all 4 codes from txt files For Each code As String In PPLCodeList MsgBox(code) MsgBox(PPLCode) If PPLCode.Trim = code.Trim Then -- here i am trying to compare with Array value and txt file value. but i am able to compare all the array values to first code from first file, after that package gets finished, its not looping to the send code of second txt file. MsgBox(" Found a match") Exit For Else MsgBox("No Match") End If Next Else MsgBox("File Does Not Exist") End If Dts.TaskResult = Dts.Results.Success End Sub End Class Please help me , i have listed my question next to code i am getting issue.

Are you trying to compare data in file with data in a table?

Yes.... Data in table i have stored as an Array, and reading data from file to compare with array value.

I think you dont need to do such complicated script logic then, use the steps below: In data flow task 1. Get file data using Flat file source 2. Add a lookup and point that lookup to the table you want to compare 3. In the lookup join the columns you want to compare 4. If you want to fail if there are mismatches then select “Fail component” …(check the logic you want here) 5. Destination can be a table or file you want to drop your failed comparison data or matched (based on your logic) If this is not clear then let me know your logic on comparison, I can help then.

hi.. I have like 128 files.. Each file has a Header record, Column Names, Data, Trailor record. The data i need to compare is in Header record. For Ex; The header record is like this REPORT FOR PART FAMILY: ADAPTERS (SYSTEM TO SYSTEM) DATE:2011.Mar.15 PPL CODE:HSEF-001-AAA I am comparing the PPL Code . So IF the PPL code in file and table matches then i have to load that file. I don`t want to load all the files. So this is my exact requirement, Sorry for the trouble, that is why i am reading only header record from all files in For each loop container and trying to compare. If there is any better way please let me know.

Are these files huge? Also one more question - is PPLCode in the table spread over multiple columns (do we need to loop through all columns to get ppl code?)

Not so Huge.. PPL Code : HSEF-001-AAA for now i am comparing last three digits (AAA) from file and in table i have (AAA) in one column. In database table i have pplcode in one column. Thanks

Hummm..since you have to compare header only( top row) you are doing it corretly... since you said that PPL code is in only one column ...then basically we dont need to loop through all the columns in table, only one column will be sufficient.... see below flow I am expecting to be designed.. ( i will check the script code and reply soon) 1.Execute SQL task will fetch only the PPL column into a object variable (select ppl_task from table) 2.For each loop will be configured to loop through each file and pass the file name into a varibale used in the script task and data flow task within. 3.Script task to open the file and pull only the header row ( this i think you have coded correctly). Next get the variable and only loop through one column. Script task to have one readwrite paramater. If the PPL code is found set it as true (or 1) 4.Create data flow task to load the file 5.Join the script task to data flow task and change the constraint to check the variable set by script task to decide to load the file or not (see the dotted lines ) this will only load the files having pplcode in the table.

Hey..just thought of better way…we don’t need the variable to load the table check if this helps.... Have the for loop, script task and data flow task in place In the script task we will get the header and build the SQL to check the PPL code… Set the flag for the constraint to decide to load the file. 'below use the code to fetch PPL code from file header Dim file_pplcode As String Dim filecon As New System.IO.StreamReader("c:\harsh\tmp\1.txt") file_pplcode = filecon.ReadLine() MessageBox.Show("File pplcode :" + file_pplcode) 'below use the code to query the table and use ppl code fetched from file in where clause Dim conn As New SqlClient.SqlConnection("Data Source=.;Initial Catalog=tmp;Integrated Security=SSPI;") Dim cmd As New SqlClient.SqlCommand() Dim flag As Int16 cmd.Connection = conn cmd.CommandText = "select count(1) from table_1 where ppl_code = '" + file_pplcode + "' " conn.Open() flag = cmd.ExecuteScalar() conn.Close() 'if the ppl code is found i.e count will be > 0 then set the variable to be used the constraint If flag > 0 Then Dts.Variables("var").Value = 1 Else Dts.Variables("var").Value = 1 End If

Hi.. ' 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 System.IO Imports Microsoft.SqlServer.Dts.Runtime Imports System.Windows.Forms Imports System.Xml Imports System.Collections Imports System.Data.OleDb Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper 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 file_pplcode As String 'Dim filecon As New System.IO.StreamReader("\\Rich-msql-57-rv\e$\CNH\Data Feeds\eMatrix\ePRW\Automated Import\Sample_Files\Testing\PartListFor_BEARING-CONES.txt") Dim FILE_NAME As String FILE_NAME = Dts.Variables("eprwtesting").Value.ToString() '+ "\" + Dts.Variables("FileName").Value.ToString() Dim Firstcol As Integer Dim Secondcol As Integer Dim Lastcol As Integer Dim Line As String Dim LineRev As String Dim EmatrixFamilyName As String Dim PPLCode As String Dim dt As New DataTable Dim oleDA As New OleDbDataAdapter Dim row As DataRow Dim col As DataColumn If System.IO.File.Exists(FILE_NAME) = True Then Dim objReader As New System.IO.StreamReader(FILE_NAME) Line = Line & objReader.ReadLine() & vbNewLine 'MsgBox(Line) LineRev = StrReverse(Line) Firstcol = Line.IndexOf(": ") Secondcol = Line.IndexOf(" DATE:") Lastcol = LineRev.IndexOf("-") EmatrixFamilyName = Line.Substring(Firstcol + 1, Secondcol - (Firstcol + 1)) file_pplcode = StrReverse(LineRev.Substring(0, Lastcol)) 'MsgBox(EmatrixFamilyName) 'MsgBox(PPLCode) 'file_pplcode = filecon.ReadLine() MsgBox("File pplcode :" + file_pplcode) End If ' Up to here it worked..i am getting file_PPlcode , when i use below code i am getting error The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. server: Rich-msql-40-rv database name CNHStaging 'below use the code to query the table and use ppl code fetched from file in where clause Dim conn As New SqlClient.SqlConnection("Data Source=Rich-msql-40-rv;Initial Catalog=CNHStaging;Integrated Security=SSPI;") Dim cmd As New SqlClient.SqlCommand() Dim flag As Int16 cmd.Connection = conn cmd.CommandText = "select count(1) from eprw_pplcode where ppl_code = '" + file_pplcode + "' " conn.Open() flag = CShort(cmd.ExecuteScalar()) conn.Close() 'if the ppl code is found i.e count will be > 0 then set the variable to be used the constraint If flag > 0 Then Dts.Variables("var").Value = 1 Else Dts.Variables("var").Value = 1 End If Dts.TaskResult = Dts.Results.Success End Sub End Class Please have a look,

Make sure that you have mapped the variable in the script task editor..see below... (create integer variable in the package level)

Sorry was in meeting.. Yes..i have created and Var variable with Int16 datatype and assigned to for each loop but still The type of the value being assigned to variable "User::var" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. I am getting this error.

It worked ..now i am working on Dataflow task..

great... you do have a intresting requirement ... enjoy :)

Thank you very much for your help, I can able to load into table where PPLCode matches. I am loading the files as one column. Dont know this helps or not, Because each file has different number of columns. Each file has column names in the second row., may be i need to do a dynamic mapping of columns. I have one more table with mapping details. source column name and destinantion colun name are different and mapping is provided in thistable. Do you have any idea of mapping dynamically and load into destination table? As i said before all files dont have equal number of columns. One has 12 columns other has 15 columns like that. If you dont mind can i have your email . Thanks

great to see it worked... dynamic column mapping is not possible i can say or is very very complicated...simpler solutions can be designed as a way around.. I had one such instance where the colums were not fixed..in such case we use to stage the file in one column table and then use tsqls to split the columns based on the complex logic to decrypt the files :D. Let me know if i can be of any help.. you can reach me @ harshharsh26@yahoo.com