June 10, 2010 at 10:55 am
I am new to SSIS and I need to query a list of tables from a table. Then from that list create a CSV file from each table in the list.
So far I was able to do this for only one table by creating a Data Flow Task, then an OLE DB source with data access mode Table or View and a certain table selected, then going to a Flat File destination. But I need to be able to query the table that contains all the tables and feed that into the OLE DB source (or maybe some other type of source) somehow I think.
So the query I would use to pull the list of tables would be:
select ApplianceTypeTableName from appsystem.ApplianceTypes
Any help from here would be very much appreciated.
June 10, 2010 at 11:19 am
It looks like you are going to have to write a for-each loop that loops through a recordset.
you can populate this recordet by querying your table that contains the table names and assign the table name to a variable.
Then you should be able to use this varaible to dynamically generate your source SQL query for each table within the loop
June 10, 2010 at 11:30 am
Unless all the tables have the same structure, you'll probably be better off doing this dynamically. SSIS loads the column mappings for the export, and if they're changing, it will fail.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 10, 2010 at 12:27 pm
All of the tables do have a different structure.
June 16, 2010 at 2:30 am
I had a similar problem, where I needed to output a number of reports as tab-separated text files; each was based on a view, and I wrote a script task to output them all. (BTW I prefer TSV to CSV as you don't have to worry about commas within your text).
Here is the function I use to output each report; it will work with table names as well as view names.
' ---------------------------------------------------------------------------------------------------------------------------------------- '
Function OutputReport(ByVal ReportView As String, ByVal DataPath As String) As Boolean ' Returns TRUE if it runs OK
' Output the view or table as a tab-separated text file in DataPath. The file name is based on the view name.
' The first row in the output file contains the field header names
Dim RecordCount As Integer = 0
OutputReport = False ' Return false if function fails
Try
If Connection Is Nothing Then Connection = New SqlConnection(ConnectionString)
If Connection.State = ConnectionState.Closed Then Connection.Open()
' Check Output directory
If Dir(DataPath, FileAttribute.Directory) = "" Then MkDir(DataPath) ' Ensure DataPath exists
' Open the file - ensuring any spaces in the view name are replaced by _
Dim File As String = String.Format("{0}\{1}.txt", DataPath, ReportView.Replace(" ", "_"))
Dim Writer As New IO.StreamWriter(File)
' Run the query
Dim SQL As String = String.Format("SELECT * FROM MyDB.dbo.[{0}];", ReportView)
Dim SqlCmd = New SqlCommand(SQL, Connection)
SqlCmd.CommandTimeout = 360 ' Ensure enought time to output the report!
Using Reader As SqlDataReader = SqlCmd.ExecuteReader()
Dim Fields(Reader.FieldCount - 1) As String
' Output header row containing the field names
For i = 0 To Reader.FieldCount - 1
Fields(i) = Reader.GetName(i).Replace("_", " ")
Next i
Writer.WriteLine(Join(Fields, vbTab)) ' Or use "," if you want CSV
' Now output the Data
While Reader.Read()
For i = 0 To Reader.FieldCount - 1
Fields(i) = Reader(i).ToString
Next i
Writer.WriteLine(Join(Fields, vbTab))
RecordCount += 1
End While
Reader.Close()
Writer.Close()
MsgBox(String.Format("{0} records written to {1}", RecordCount, IO.Path.GetFileName(File)))
End Using
Return True
Catch
MsgBox(String.Format("OutputReport for {0} row {1}; Error: {2}", ReportView, RecordCount, Err.Description))
End Try
End Function
' ---------------------------------------------------------------------------------------------------------------------------------------- '
You could write something that gets the list of the tables you want to output, and calls the above function from a ' For Each Report In ...' loop.
June 16, 2010 at 4:00 am
Thanks SSC Rookie. I ended up solving my own problem with this. I wrote a script in SQL Server to output to CSV using BCP. The only problem was the BCP does not put the column header info as the first row in the CSV file so I had to write this routine to do it. It can be used generically by others who want to export all or some of their tables to CSV.
set NOCOUNT ON
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
DECLARE @TableID int, @TableName varchar(500), @ColumnName varchar(500)
DECLARE @TempTableColumnList varchar(max), @InsertColumnList varchar(max)
DECLARE @SelectColumnList varchar(max), @HeaderColumnList varchar(max)
DECLARE @BCP_Command nvarchar(4000), @TableCreate nvarchar(4000), @InsertCommand nvarchar(4000)
DECLARE Tables_CURSOR CURSOR FOR
SELECT Name [TableName],[ID]
FROM dbo.sysobjects
WHERE xtype = 'U'
ORDER BY Name
OPEN Tables_CURSOR
FETCH NEXT FROM Tables_CURSOR INTO @TableName, @TableID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Table creation string
SET @TableCreate = 'CREATE TABLE ##Temp ( '
-- Create Cursor to pull Columns
DECLARE Columns_Cursor CURSOR FOR
SELECT Name [ColumnName] FROM dbo.syscolumns
WHERE [id] = @TableID
ORDER BY [colid]
OPEN Columns_CURSOR
FETCH NEXT FROM Columns_CURSOR INTO @ColumnName
-- Initialize List variables
SET @TempTableColumnList = ''
SET @InsertColumnList = ''
SET @HeaderColumnList = ''
SET @SelectColumnList = ''
-- Build List variables
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TempTableColumnList = @TempTableColumnList + @ColumnName + ' VARCHAR(5000)'
SET @InsertColumnList = @InsertColumnList + @ColumnName
SET @HeaderColumnList = @HeaderColumnList + '''' + @ColumnName + ''''
SET @SelectColumnList = @SelectColumnList + 'CONVERT( varchar,' + @ColumnName + ')'
FETCH NEXT FROM Columns_CURSOR INTO @ColumnName
IF @@FETCH_STATUS = 0
BEGIN
SET @TempTableColumnList = @TempTableColumnList + ', '
SET @InsertColumnList = @InsertColumnList + ', '
SET @HeaderColumnList = @HeaderColumnList + ', '
SET @SelectColumnList = @SelectColumnList + ', '
END
END
-- Create the temp table
SET @TableCreate = @TableCreate + @TempTableColumnList + ' )'
EXEC sp_executesql @Tablecreate
-- Build INSERT Command for header
SET @InsertCommand = 'INSERT INTO ##Temp ( ' + @InsertColumnList + ') '
SET @InsertCommand = @InsertCommand + 'VALUES ( ' + @HeaderColumnList + ')'
EXEC sp_executesql @InsertCommand
-- Build INSERT Command for all other CSV data
SET @InsertCommand = 'INSERT INTO ##Temp ( ' + @InsertColumnList + ') '
SET @InsertCommand = @InsertCommand + 'SELECT ' + @SelectColumnList + ' FROM AppLookUp.' + @TableName
EXEC sp_executesql @InsertCommand
-- Create Output CSV file using BCP
SET @BCP_Command = 'BCP ##Temp OUT C:\ApplianceCSV\' + @TableName + '.CSV -o c:\ofile.txt -c -t, -T -S' + @@servername
exec master..xp_cmdshell @BCP_Command
DROP TABLE ##Temp
CLOSE Columns_CURSOR
DEALLOCATE Columns_CURSOR
FETCH NEXT FROM Tables_CURSOR INTO @TableName, @TableID
END
CLOSE Tables_CURSOR
DEALLOCATE Tables_CURSOR
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply