Exporting data from multiple SQL tables to flat files using SSIS

SELECT name 

FROM sysobjects 

WHERE type = 'U'

SELECT        name

 FROM     sys.columns 

WHERE        (object_id = OBJECT_ID('test1'))

SELECT  (sys.schemas.name + '.' + sys.objects.name) AS tablename

FROM    sys.objects 

INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

Where type ='u'

-------------------------------------

is the structure of data in all tables same? I mean number of columns, name of columns  , data types.
if yes, you can use a foreach loop, to loops through tables first ,and inside foreach loop container use a data flow task with oledb source as sql server table, and flat file destination .
But if structure isn't same,you should do it as follow:
first of all create three package scope variable:
first one: Package scope, datatype Object , name TablesList
second one: package scope , datatype string , name TableName
third one: package scope, datatype string, name BCPCommand

(Note that you should set a default valid table name as a default value for TableName variable)

then add a data flow task to the control flow , inside data flow task add an oledb source, and set a connection to source database, and set the table which contains name of other tables to export inside ,
then add a RecordSet destination , and connect green arrow from oledb source to this destination, then double click on recordset destination, and set the variable as User::TablesList. also go to column mappings tab, and map the column which have name of tables inside to destination.

then go back to control flow
add a foreach loop container after data flow, and set enumerator as ADO enumerator,
and set source variable as TablesList
then go to variable mappings tab, and set variable as TableName there, and set index as 0
then click ok

then go to variables pane, 
and click on BCPCommand variable, and go to properties window, in the expression property write this:
"BCP databasename.schemaname."+ @[User::Ta bleName]+"  out c:\"+@[User::TableName]+". txt -c -t, -T –S"
also set the EvaluateAsExpression property to true.

then add an execute sql task inside foreach loop container,
and set up a connection to source database,
then set source type as variable,
and set source variable as BCPCommand

------------------------------------------------------------------------------

BCP - Export data to Text File

Here is a simple method of exporting all the data from SQL Server table to a Text File

CREATE Procedure BCP_Text_File
(  
@table varchar(100),  
@FileName varchar(100)  
)  
as  
If exists(Select * from information_Schema.tables where table_name=@table)
    Begin
        Declare @str varchar(1000)  
        set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c'''  
        Exec(@str)  
    end
else
    Select 'The table '+@table+' does not exist in the database'

 

Execute this procedure by giving Table Name and the File Name

EXEC BCP_Text_File 'Employee','C:\emp.txt'

Now all the data from Employee table will be exported to the text file which will be located at C:\emp.txt

-----------------------------------------------------------------------------------

1-The first step is to identify what are the views that we be exporting.

• 29 KB
• Views

2-Check the columns count for each of them, just to illustrate the different in structure.

 

SELECT A.name AS ViewName
	 , count(B.TABLE_NAME) AS ColCount
FROM
	sys.objects AS A
	LEFT OUTER JOIN
		INFORMATION_SCHEMA.COLUMNS AS B
		ON A.name = B.TABLE_NAME
GROUP BY
	A.name
  , A.type
HAVING
	(A.type = 'v')
ORDER BY
	ColCount DESC

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select allOpen in new window

3-I'll create 4 Variables, I love to always to the proper naming convention to tell the data type of the variable.

 

• Variables

A-Variable "ObjAllViews" will hold back the result set which has all the views' Names.
B-Variable "StrDelimiter"   will hold the delimiter that you want to use between columns, I love to use the Vertical Line "|" because most of our data has commas in it, you can get it by pressing (SHIFT + \ )
C-Variable "StrViewName" will hold each individual view.
D-Variable "StrFilePath" will hold the desired path to save the files.

4-Create a connection to your server, in my case it's 'localhost' as I've my testing SQL server on the same machine, and I named the connection 'Localhost', take note of naming the connection because it's mentioned later in the script. task.

oh...so now you get the idea?  we will use the "Execute SQL Task" to get us the list of the views that exists in that table into "ObjAllViews"  variable, then we will use "Foreach Loop Container" to shred resultset from "ObjAllViews"  variable to each individual view name into "StrViewName" variable .

Then inside the "Foreach Loop Container" we will place a "Script. Task" that will do the trick for us.

  

Now let's go over each tasks and how it's configured....

1-The control flow and the connection

• ControlFlow and connection

2-Execute SQL Task

 

• EST1

 

• EST2

3-Foreach Loop Container

 

• FEL1

 

• FEL2

4-Script. Task , Thanks for SIVA for helping me with the counter code.

 

• 89 KB
• ST

 5- add This code

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

 _
 _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()

        'Variable collection to hold the variables
        Dim VarCol As Variables = Nothing

        'Lock the 3 variables for read
        Dts.VariableDispenser.LockForRead("User::StrViewName")
        Dts.VariableDispenser.LockForRead("User::StrDelimiter")
        Dts.VariableDispenser.LockForRead("User::StrFilePath")


        'Fille the Variable collection
        Dts.VariableDispenser.GetVariables(VarCol)

        'Getting the variables' values, so we can use it later
        Dim ViewName As String = VarCol("User::StrViewName").Value.ToString()
        Dim delimiter As String = VarCol("User::StrDelimiter").Value.ToString()
        Dim FilePath As String = VarCol("User::StrFilePath").Value.ToString()


        'Construct the file name, example output: D:\vTargetMail.TXT
        Dim FName As String = FilePath & ViewName & ".TXT"

        'Build our Query
        Dim Query As String = "SELECT * FROM " & ViewName


        'Using StreamWriter
        Dim writer As StreamWriter = Nothing

        'Use our OLEDB COnnection
        Dim connection As leDbConnection = New OleDbConnection(Dts.Connections("Localhost").ConnectionString)
        Dim command As leDbCommand = Nothing
        Dim reader As leDbDataReader = Nothing

        Try
            'Checking for the file, delete if exist(you can append or rename with [File.Move(FName, Today() & "_" & FName)]
            If File.Exists(FName) Then
                File.Delete(FName)
            End If
            'Open the OLEDB connection
            connection.Open()

            'Run the query
            command = New OleDbCommand(Query, connection)
            reader = command.ExecuteReader()

            If reader.HasRows Then

                'Stream Writer using the FNAME that we declared erlier
                writer = New System.IO.StreamWriter(FName)
                While reader.Read()

                    'Counter to get the columns number
                    Dim counter As Integer = 0
                    Dim fieldCount As Integer = reader.FieldCount - 1

                    While counter <= fieldCount
                        If counter <> fieldCount Then

                            writer.Write(reader(counter).ToString() & delimiter)
                        Else
                            writer.WriteLine(reader(counter).ToString())
                        End If
                        counter += 1
                    End While
                End While
            End If
        Catch ex As Exception
            Dts.Events.FireError(1, "", "Something Wrong happened!!!", "", 0)
        Finally
            connection.Close()
            writer.Close()
        End Try
        Dts.TaskResult = ScriptResults.Success
    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:

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7899089/viewspace-732304/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7899089/viewspace-732304/