Extracting data from multiple tables to flat files using SSIS

Sometimes experts skim over questions and may not answer them immediately  It has been a few days though at this point.

It is true that if the tables contain different columns or data types, called metadata, the tables have to be explicitly created within a data source.  A Data Flow Task will always contain a source, a destination and may contain transformations, all of which send data through the "pipeline".  The pipeline always has to have a specific set of metadata assigned to it.  It's done this way because of validation, which needs to know what the columns and data types are at design-time and run-time.

A Script Task can be used to create a data table that you can fill with the specific table you're looking for, and then it can write that data table out to a text file.  A loop in the Script Task can loop over the tables needed.  Unfortunately, that really negates the need to use SSIS at that point, unless other processing is going on that SSIS is needed for.

If you need any help with creating a Script Task for it, let me know.

Hello Steith,
Thanks a lot for a detailed reply. I am newbe in this area.
I shall be very thankful to you and badly needed too for that scrpipt task. Kindly help me.
Table1, table2, table3 ....table12.... all the queries are select * from table1, select * from table2....like that all the tables. All the tables have different columns,
Thanks in advance. Really waiting for the help with lot of hopes to save my skin at work.

This code should do what you're looking for, though there's still a lot to be desired with this setup.  Take a look at it and see if it fits your need.  The only thing you'll probably need to change is the Initial Catalog in the connection string and the path as well.  Also, if you're using a connection manager, you can grab it's connection string by doing:

cn = New SqlConnection(Dts.Connections("ConnectionManagerName").ConnectionString)

    Public Sub Main()
        Dim ShowHeadings As Boolean = True
        Dim TextQualified As Boolean = True
        Dim delimiter As String = ", "
        Dim FilePath As String = "C:\"
        Dim result As Integer = Dts.Results.Success 
        Dim cn As SqlConnection
        Dim cm As SqlCommand
        Dim sw As StreamWriter
        Dim reader As SqlDataReader 
        Try
            cn = New SqlConnection("Data Source=(local);Initial Catalog=Database;Trusted_Connection=True;")
            cn.Open() 
            cm = New SqlCommand()
            cm.Connection = cn
            cm.CommandType = CommandType.Text 
            For TableNum As Integer = 1 To 12
                cm.CommandText = "SELECT * FROM Table" & CStr(TableNum)
                reader = cm.ExecuteReader(CommandBehavior.SingleResult) 
                Dim dt As New DataTable()
                dt.Load(reader) 
                sw = New StreamWriter(FilePath & "Table" & CStr(TableNum) & ".txt", False) 
                If ShowHeadings Then
                    For Each Col As DataColumn In dt.Columns
                        sw.Write(Col.ColumnName)
                        If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
                    Next 
                    sw.WriteLine()
                End If 
                For Each row As DataRow In dt.Rows
                    Dim arr As Object() = row.ItemArray
                    Dim bob As Type 
                    For ColNum As Integer = 0 To arr.Length - 1
                        Dim TypeString As String = arr(ColNum).GetType().ToString() 
                        If TypeString = "System.String" Then sw.Write("""")
                        sw.Write(arr(ColNum).ToString())
                        If TypeString = "System.String" Then sw.Write("""")
                        If ColNum <> arr.Length - 1 Then sw.Write(delimiter)
                    Next ColNum 
                    sw.WriteLine()
                Next 
                sw.Close()
            Next TableNum 
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
            result = Dts.Results.Failure
        Finally
            sw.Dispose()
            cm.Dispose()
            If Not cn.State = ConnectionState.Closed Then cn.Close()
            cn.Dispose()
        End Try 
    End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:

First of ALL , please accept my SINCERE thanks for all the help you did.

I pasted the code as it is in script task...its throwing some errors like

        Dim cn As SqlConnection - SqlConnection is not defined.
        Dim cm As SqlCommand - SqlCommand is not defined
        Dim sw As StreamWriter - Streamwriter is not defined
        Dim reader As SqlDataReader - SqlDataReader is not defined
        StreamWriter - streamwriter is not defined.

        Another thing is my tables are not defined like table1, table2 etc., they have different names each. Just an example I mentioned. Each table has different name.
How to fix these? By the way this procedure works for different tables also?

Wow... I completely forgot to mention a few other pieces that are pretty crucial.  I'll mention them in a second.  As far as the table names, it really sounded like you were using Table1, 2, etc., but I had a feeling that's not what they were exactly.  There are two ways to work with the table names.  You can either define a List or array of table names in the code and iterate through that, or you can create a ForEach Loop in the Control Flow layout.  You would then set the loop to an Item Enumerator, add a column to the collection, and fill it with table names.  Then you would assign that loop to a variable that will hold the current table name.  If you need help with any of that, let me know.

Back to the other pieces... I forgot to mention that you'll have to add a reference to the VSA project, and add a few import lines to the top of the script.  Here's how to add the references:

In the script window, go to the Project menu, and click Add Reference.  Then in the list that appears, find System.Xml (probably the last one) and click the Add button at the bottom.  Then Click OK.

To add the Import lines, just write these three lines at the top where the other Import lines are:

Import System.Data.SqlClient
Import System.IO
Import System.Xml

It doesn't matter what order you write them in.  Once you do that, the code should run fine.

I did exactly what you instructed....
Unfortunately I am getting the following error...

SSIS package "Flatfile_Nodata.dtsx" starting.
Error: 0x0 at Script Task: Keyword not supported: 'provider'.
Error: 0x2 at Script Task: The script threw an exception: Object reference not set to an instance of an object.
Task failed: Script Task

Is it possible to copy and paste the entire contents of the script into a comment?  The provider piece points to something possible with the connection string for the SqlConnection variable.  Did you try to do the:

cn = New SqlConnection(Dts.Connections("ConnectionManagerName").ConnectionString)

or use the original code?  The Connection Manager above is for the OleDB connection to the SQL Server database, and the error above indicates that you're using a connection manager other than that one.

' 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 Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.IO
Imports System.Xml

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 ShowHeadings As Boolean = True
        Dim TextQualified As Boolean = True
        Dim delimiter As String = "^^"
        Dim FilePath As String = "C:\"
        Dim result As Integer = Dts.Results.Success
        Dim cn As SqlConnection
        Dim cm As SqlCommand
        Dim sw As StreamWriter
        Dim reader As SqlDataReader
        Try
            cn = New SqlConnection(Dts.Connections("INDIA.DBA").ConnectionString)

            cn.Open()
            cm = New SqlCommand()
            cm.Connection = cn
            cm.CommandType = CommandType.Text
            For TableNum As Integer = 1 To 12
                cm.CommandText = "SELECT * FROM DBA.dbo.invoices" & CStr(TableNum)
                reader = cm.ExecuteReader(CommandBehavior.SingleResult)
                Dim dt As New DataTable()
                dt.Load(reader)
                sw = New StreamWriter(FilePath & "invoices" & CStr(TableNum) & ".txt", False)
                If ShowHeadings Then
                    For Each Col As DataColumn In dt.Columns
                        sw.Write(Col.ColumnName)
                        If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
                    Next
                    sw.WriteLine()
                End If
                For Each row As DataRow In dt.Rows
                    Dim arr As Object() = row.ItemArray
                    Dim bob As Type
                    For ColNum As Integer = 0 To arr.Length - 1
                        Dim TypeString As String = arr(ColNum).GetType().ToString()
                        If TypeString = "System.String" Then sw.Write("""")
                        sw.Write(arr(ColNum).ToString())
                        If TypeString = "System.String" Then sw.Write("""")
                        If ColNum <> arr.Length - 1 Then sw.Write(delimiter)
                    Next ColNum
                    sw.WriteLine()
                Next
                sw.Close()
            Next TableNum
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
            result = Dts.Results.Failure
        Finally
            sw.Dispose()
            cm.Dispose()
            If Not cn.State = ConnectionState.Closed Then cn.Close()
            cn.Dispose()
        End Try

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

My connection name is INDIA.DBA
Servername is INDIA
Database name is DBA
tables names are invoices1, invoices2, invoices3, .....

It looks like the connection string for an OLEDB Connection Manager is in the OleDbConnection format, which actually makes a lot of sense.  In that case, you can keep the SqlConnection object, and just specify the connection string right in the code like my example originally does.

If you don't want to do that, you'll have to change the SqlConnection object to an OleDbConnection object.  I haven't tested if it works but I can throw it in my code and see if it works.  You can also try it but I don't know if a SqlCommand object can use an OleDbConnection object.  It uses the provider identifier so it should work with the Connection Manager's connection string.

For our environment I think Oledbconnection is needed but not sure.
How can I change Sqlconnection object o an OleDbconnection object?

This is what I have now, and it tested 100% successfully.  It's the whole thing so do a select all and paste this code in.  It used my connection manager that I set up on my package, so it should work with your connection manager too if it's OLE DB.  Let me know how it works for you.  Also keep in mind that this package is checking 12 tables.  Make sure you have 12 invoices tables before running  it because if you have less it will fail again.

' 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.Data.OleDb
Imports System.IO
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime 
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 ShowHeadings As Boolean = True
        Dim TextQualified As Boolean = True
        Dim delimiter As String = "^^"
        Dim FilePath As String = "C:\"
        Dim result As Integer = Dts.Results.Success 
        Dim cn As OleDbConnection
        Dim cm As OleDbCommand
        Dim sw As StreamWriter
        Dim reader As OleDbDataReader 
        Try
            cn = New OleDb.OleDbConnection(Dts.Connections("INDIA.DBA").ConnectionString)
            cn.Open() 
            cm = New OleDbCommand()
            cm.Connection = cn
            cm.CommandType = CommandType.Text 
            For TableNum As Integer = 1 To 12
                cm.CommandText = "SELECT * FROM DBA.dbo.invoices" & CStr(TableNum)
                reader = cm.ExecuteReader(CommandBehavior.SingleResult) 
                Dim dt As New DataTable()
                dt.Load(reader) 
                sw = New StreamWriter(FilePath & "Invoices" & CStr(TableNum) & ".txt", False) 
                If ShowHeadings Then
                    For Each Col As DataColumn In dt.Columns
                        sw.Write(Col.ColumnName)
                        If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
                    Next 
                    sw.WriteLine()
                End If 
                For Each row As DataRow In dt.Rows
                    Dim arr As Object() = row.ItemArray
                    Dim bob As Type 
                    For ColNum As Integer = 0 To arr.Length - 1
                        Dim TypeString As String = arr(ColNum).GetType().ToString() 
                        If TypeString = "System.String" Then sw.Write("""")
                        sw.Write(arr(ColNum).ToString())
                        If TypeString = "System.String" Then sw.Write("""")
                        If ColNum <> arr.Length - 1 Then sw.Write(delimiter)
                    Next ColNum 
                    sw.WriteLine()
                Next 
                sw.Close()
            Next TableNum 
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
            result = Dts.Results.Failure
        Finally
            sw.Dispose()
            cm.Dispose()
            If Not cn.State = ConnectionState.Closed Then cn.Close()
            cn.Dispose()
        End Try 
    End Sub 
End Class

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:

Awesome. You are the man....! Thanks a BUNCH. I owe you a very good lunch..
It basically worked for me. Now I can convert to my environment becuase we have a table that contains tables list those are needed to be run for sql commands. As you I can try foreach loop and get back to you.
Quick question...can we implement all the flat file formats here also like ....

locale:  Unicode
Format: Delimited
Head row delimiter {CR}{LF}
row delimiter  {CR}{LF}
Also I saw every identity columns have {  }  Is it possible to implement all those things in this script?
Please let me know

For the table list, you can put a data flow task above the Script Task, and in there grab the table list in the source, and put it into a recordset destination.  Then you can put the Script Task in a ForEach Loop and use the ADO enumerator to grab that list and store it in a variable to use in the Script Task.

Yes, you actually can implement those formats.  I'm not sure about the unicode locale, though the StreamWriter object might have a property for encoding.

For the delimiter, that's already set in the script, though there's nothing set to say delimited or not.  Also, Fixed Width formatting would be more difficult to achieve, but it is possible.  The header and row delimiters can be set like this:

Dim HeadDelimiter As String = Chr(10) & Chr(13)
Dim RowDelimiter As String = Chr(10) & Chr(13)

and then instead of the sw.WriteLine() methods, you would replace those with sw.Write(HeadDelimiter) and sw.Write(RowDelimiter) in the appropriate area.

I don't know what the {  } is in reference to the identity columns.  Is it an array of data?

This script task is definitely starting to sound more and more like a custom task, though they're not simple to create so it would only be beneficial if this script was used over and over in multiple packages.

For the encoding type, you can add this line anywhere before the StreamWriter is initialized:

    Dim EncodingType As System.Text.Encoding = System.Text.Encoding.Unicode

and then change the sw variable to this:

    sw = New StreamWriter(FilePath & "Invoices" & CStr(TableNum) & ".txt", False, EncodingType)

You can use any of the properties in the System.Text.Encoding class besides Unicode.  You can also add an Imports line for System.Text so you don't have to keep writing it out.

I am almost done with the script except that {  before and } after uniqueidentifier values.
by the way, Is it {CR}{LF} Chr(10) & Chr(13) or Chr(13) & Chr(10)
I am getting all the rows as single row...am I doing something wrong?

As far as I'm aware, it should be

Dim HeadDelimiter As String = Chr(10) & Chr(13)
Dim RowDelimiter As String = Chr(10) & Chr(13)

and then you would replace the WriteLine methods with this:

sw.Write(HeadDelimiter)
sw.Write(RowDelimiter)

That would go in place of the WriteLine methods, like in the code below.  There's a property in the StreamWriter that's called NewLine.  It has the {CR} {LF} delimiter in there by default, and you could use that, but then you wouldn't be able to specify a header and a row one independently.

If it still all goes on one line, let me know.

Dim HeadDelimiter As String = Chr(10) & Chr(13)
 
If ShowHeadings Then
     For Each Col As DataColumn In dt.Columns
         sw.Write(Col.ColumnName)
         If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
     Next 
sw.Write(HeadDelimiter)
End If 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Hi Stelth,
I am getting everything as expected except two thins...can you help me for that too please?

1. I am getting uniqueidentifier as it is where as if I do by DTS package for table I am getting { }
     example: with the  script I am getting the value  as ====>   0g0e30c9-8eae-4g79-9882-07cg8bc5c10b
                    with DTS pack I am getting the value as ====>  {0G0E30C9-8EAE-4G79-9882-07CG8BC5C10B}

Why is that difference?

2. Date format is also different...
     example:   with the script I am getting====>   10/7/2009 7:25:39 AM
                       with dts package I am getting as====>   2009-10-07 07:25:39.507000000

Why is that?
Is it possible to fix this? Kindly let me know. I know you worked a lot for me. Really I appreciate. Thanks in advance.

This question is definitely getting more complex, especially for only 125 pts.  Is it possible to maybe raise the question points?  I definitely want to keep helping out, but for the amount of work involved and points being the only compensation to show for it, it would definitely be nice.

I'm not too sure what you mean when you say dts.  Are you talking about the SSIS package or an old dts package?  If you're talking about an SSIS package, were you actually sending the table through a data flow task to test the data?  If that's true, then it seems like the script is changing how the formatting is set.  I've never worked with uniqueidentifier types before, only datetime types.  The datetime type never looked like what the "dts package" line shows.  Though with the script the AM doesn't make sense either... it should really be mm/dd/yyyy hh:MM:ss.mmm.

I'm going to test the data in a little bit and get back to you.  It seems like the two data types act differently within SSIS than they do in .NET.

Thanks for your  reply.
Yes...DTS means SSIS...sorry about writing DTS. Definitely I can give more points Really you helped me.  I want you make really happy in terms of pointsfor all the help. If you are in texas I love to meet you for giving good party for you.

By the way how can I raise the points?

You can raise the points with the Increase Points button right near the comment box.  It has the number of points in the question currently and can be changed to any number up to 500.  This question is definitely more of a 500 point question, though the amount is totally up to you, especially if you have limited points to spend.

I have to head out in a bit so I may not get any immediate responses but I'll check back soon.

The date format is not working. I am getting the same old format.

It worked great for me.  Must have been mistyped within the code.  Here's the full code I used including the text qualifier, the GUID braces, and the datetime check.  It's the whole script so you can replace everything in your script with it... hopefully you haven't changed anything else in it yet.

' 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.Data.OleDb
Imports System.IO
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
 
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 ShowHeadings As Boolean = True
        Dim ShowGuidBraces As Boolean = True
        Dim TextQualified As Boolean = True
        Dim delimiter As String = "^^"
        Dim FilePath As String = "C:\"
        Dim result As Integer = Dts.Results.Success
        Dim EncodingType As System.Text.Encoding = System.Text.Encoding.Unicode
 
        Dim cn As OleDbConnection
        Dim cm As OleDbCommand
        Dim sw As StreamWriter
        Dim reader As OleDbDataReader
 
        Try
            cn = New OleDb.OleDbConnection(Dts.Connections("INDIA.DBA").ConnectionString)
            cn.Open()
 
            cm = New OleDbCommand()
            cm.Connection = cn
            cm.CommandType = CommandType.Text
 
            For TableNum As Integer = 1 To 12
                cm.CommandText = "SELECT * FROM dbo.invoices" & CStr(TableNum)
                reader = cm.ExecuteReader(CommandBehavior.SingleResult)
 
                Dim dt As New DataTable()
                dt.Load(reader)
 
                sw = New StreamWriter(FilePath & "invoices" & CStr(TableNum) & ".txt", False, EncodingType)
 
                If ShowHeadings Then
                    For Each Col As DataColumn In dt.Columns
                        sw.Write(Col.ColumnName)
                        If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
                    Next
 
                    sw.WriteLine()
                End If
 
                For Each row As DataRow In dt.Rows
                    Dim arr As Object() = row.ItemArray
                    Dim bob As Type
 
                    For ColNum As Integer = 0 To arr.Length - 1
                        Dim TypeString As String = arr(ColNum).GetType().ToString()
 
                        If TextQualified AndAlso TypeString = "System.String" Then sw.Write("""")
                        If ShowGuidBraces AndAlso TypeString = "System.Guid" Then sw.Write("{")
 
                        If TypeString = "System.DateTime" Then
                            sw.Write(CDate(arr(ColNum)).ToString("MM/dd/yyyy HH:mm:ss.fff"))
                        Else
                            sw.Write(arr(ColNum).ToString())
                        End If
 
                        If ShowGuidBraces AndAlso TypeString = "System.Guid" Then sw.Write("}")
                        If TextQualified AndAlso TypeString = "System.String" Then sw.Write("""")
 
                        If ColNum <> arr.Length - 1 Then sw.Write(delimiter)
                    Next ColNum
 
                    sw.WriteLine()
                Next
 
                sw.Close()
            Next TableNum
 
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
            result = Dts.Results.Failure
        Finally
            sw.Dispose()
            cm.Dispose()
            If Not cn.State = ConnectionState.Closed Then cn.Close()
            cn.Dispose()
        End Try
 
    End Sub
 
End Class

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:

Thank you very much. Really I appreciate all the help.
Here is the thing ....
I am not getting data from table which has the column type varbinary (256)
Just getting the word  System.Byte[]  in the place of the data for that column.
Other than that every thing is perfect.

I don't know a lot about the varbinary column, only how to convert it.  The code to convert the value is a rathelastly, it r long statement:

sw.Write("0x" & BitConverter.ToString(CType(arr(ColNum), System.Byte())).Replace("-", String.Empty))

What this does is it takes the column data, converts it to a System.Byte array, then displays it as a string using the BitConverter class.  This is necessary because the System.Byte() value would have to be looped over to get the values which would then have to be concatenated together.  BitConverter does all that for you.  Finally, a 0x is added to the beginning, and BitConverter outputs each hex value with a dash, so the replace command removes those.

Semi-complicated, but easier than it could be.  I'm not sure if that's the format you're looking for (0xA12B34C56D78E90F) but that's what will output.  The full code is below.  I replaced the TypeString piece with the Typeof...Is operator so the check is more managed.

' 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.Data.OleDb
Imports System.IO
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
 
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 ShowHeadings As Boolean = True
        Dim ShowGuidBraces As Boolean = True
        Dim TextQualified As Boolean = True
        Dim delimiter As String = "^^"
        Dim FilePath As String = "C:\"
        Dim result As Integer = Dts.Results.Success
        Dim EncodingType As System.Text.Encoding = System.Text.Encoding.Unicode
 
        Dim cn As OleDbConnection
        Dim cm As OleDbCommand
        Dim sw As StreamWriter
        Dim reader As OleDbDataReader
 
        Try
            cn = New OleDb.OleDbConnection(Dts.Connections("INDIA.DBA").ConnectionString)
            cn.Open()
 
            cm = New OleDbCommand()
            cm.Connection = cn
            cm.CommandType = CommandType.Text
 
            For TableNum As Integer = 1 To 12
                cm.CommandText = "SELECT * FROM dbo.invoices" & CStr(TableNum)
                reader = cm.ExecuteReader(CommandBehavior.SingleResult)
 
                Dim dt As New DataTable()
                dt.Load(reader)
 
                sw = New StreamWriter(FilePath & "invoices" & CStr(TableNum) & ".txt", False, EncodingType)
 
                If ShowHeadings Then
                    For Each Col As DataColumn In dt.Columns
                        sw.Write(Col.ColumnName)
                        If Col.Ordinal <> dt.Columns.Count - 1 Then sw.Write(delimiter)
                    Next
 
                    sw.WriteLine()
                End If
 
                For Each row As DataRow In dt.Rows
                    Dim arr As Object() = row.ItemArray
                    Dim bob As Type
 
                    For ColNum As Integer = 0 To arr.Length - 1
                        Dim Col As Object = arr(ColNum)
 
                        If TextQualified AndAlso TypeOf Col Is System.String Then sw.Write("""")
                        If ShowGuidBraces AndAlso TypeOf Col Is System.Guid Then sw.Write("{")
 
                        If TypeOf Col Is System.DateTime Then
                            sw.Write(CDate(arr(ColNum)).ToString("MM/dd/yyyy HH:mm:ss.fff"))
                        ElseIf TypeOf Col Is System.Byte() Then
                            sw.Write("0x" & BitConverter.ToString(CType(arr(ColNum), System.Byte())).Replace("-", String.Empty))
                        Else
                            sw.Write(arr(ColNum).ToString())
                        End If
 
                        If ShowGuidBraces AndAlso TypeOf Col Is System.Guid Then sw.Write("}")
                        If TextQualified AndAlso TypeOf Col Is System.String Then sw.Write("""")
 
                        If ColNum <> arr.Length - 1 Then sw.Write(delimiter)
                    Next ColNum
 
                    sw.WriteLine()
                Next
 
                sw.Close()
            Next TableNum
 
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)
            result = Dts.Results.Failure
        Finally
            sw.Dispose()
            cm.Dispose()
            If Not cn.State = ConnectionState.Closed Then cn.Close()
            cn.Dispose()
        End Try
 
    End Sub
 
End Class

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:

Thank you very much for the reply.
Now its working fine.

Quick question before closing this thread....

Is this script generalized for a table that may contain any type of columns?

After some time the table is altered with different type of columns then this won't work?

Please let me know about the drawbacks of the script.
Appreciate your time and all the patience and kindness you showed to me.

Another question ... Is there anyway I can send personal message to you or anyone who answers the questions posted in this website?

As far as I know, not at the moment.  I know they're possibly planning to add personal messages into the site, but right now I've never seen an option to private message anyone.  I'll look around though just to make sure.  They might have something here, like in the profile section.

He is an EXCELLENT of EXCELLENT guy I ever seen upto now...really lot  of patience and service minded with good heart in help.

Hello,
Erroneously I accepted incompleted solution rather than the correct one. Any one could fix that please? The correct solution is stelth240: 's THIRD comment from bottom.