I have a select Script as follows:
SELECT c.ABC AS 'ABC'
, a.Qty AS 'Quantity_Recived'
, b.PC AS 'PC'
, b.PC AS 'PC'
, 'I' AS 'Flag'
FROM TNRInventory.dbo.tInventoryAlloc AS a
LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID
LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset
LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID
WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'
group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center
order by c.SKU_NO,vwMap.[DataSourceAllocName]
GO
i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.
I have found some document related to same issue on this url
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17
but still there is a catch.
Can any one guide/suggest me better way to do the aforesaid.
Thanks
Thinking while typing, this could be done with a few steps.
1 - Data Flow - Load a staging table with the results of the SQL. Add to it a row number counter so that every row is numbered uniquely.
2 - Control Flow - Run an Execute SQL Task to select max(rownumber) from that staging table
3 - Control Flow - Use a script task to take the output of the Execute SQL task and populate another variable with the number of iterations needed to populate files with up to 300 rows. (max(rownumber) / 300 - if no remainder, use that value, if remainder add one to the integer, etc...)
4 - Control Flow - Use a for loop to iterate the output variable from step 3 above.
5 - Control Flow - Build a variable set to an expression to use a base filename and the variable from step 3 above.
6 - Control Flow For Loop - Loop through the variable from step 3 above and add a data flow. Inside this data flow, use an OLE DB (or whatever) to connect to the staging table from step 1, filtering by (variable_step3 * 300) you can select just the records for this iteration. Hook that up to a flat file destination, which uses expressions to set the file name equal to the variable from step 5 above.
Something like that.|||
You might also check Jamie's blog post on this topic.
http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx
Essentially the same technique that Phil recommended, but he's got some sample code already 
|||
 jwelch wrote: |
|
You might also check Jamie's blog post on this topic. http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx Essentially the same technique that Phil recommended, but he's got some sample code already |
D'oh! Should've just looked there first!|||
What i did is i create a view for aforesaid script and then used that script in the following VB.net script
' 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.Text
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim ConnectionString As String = "Server=ServerName;Database=DatabaseName;uid=Login;pwd=Password;"
Dim querystring As String = "SELECT * FROM vwInventory_I"
Dim con As New SqlConnection(ConnectionString)
Dim adapter As New SqlDataAdapter()
Dim ds As New DataSet
Dim dt As New DataTable
adapter.SelectCommand = New SqlCommand(querystring, con)
adapter.Fill(ds)
dt = ds.Tables(0)
CreateFile(dt)
End Sub
Private Sub CreateFile( ByVal table As DataTable)
Dim intRowCount As Integer = table.Rows.Count
Dim Column1Value As String
Dim Column2Value As String
Dim Column3Value As String
Dim Column4Value As String
Dim Column5Value As String
Dim sb As New StringBuilder
Dim seperator As String = vbTab
Dim nFileNameCount As Integer
nFileNameCount = 0
'For i As Integer = 0 To table.Columns.Count - 1
Dim i As Integer = 0
Dim j As Integer = 0
For j = 0 To intRowCount - 1
If nFileNameCount = 100 Then
PrintFile(sb, j)
'clears the stringbuilder
sb.Remove(0, sb.ToString.Length - 1)
nFileNameCount = 0
End If
Column1Value = table.Rows(j)(i).ToString
Column2Value = table.Rows(j)(i + 1).ToString
Column3Value = table.Rows(j)(i + 2).ToString
Column4Value = table.Rows(j)(i + 3).ToString
Column5Value = table.Rows(j)(i + 4).ToString
sb.Append(Column1Value & seperator & Column2Value & seperator & Column3Value & seperator & _
Column4Value & seperator & Column5Value & seperator & vbCrLf)
nFileNameCount = nFileNameCount + 1
Next
PrintFile(sb, j)
'Next
End Sub
Private Sub PrintFile( ByVal sb As StringBuilder, ByVal recordcount As Integer)
' Create an instance of StreamWriter to write text to a file.
Dim strDate As String
strDate = String.Format("{0:yyyy}" , DateTime.Now)
strDate = strDate & String.Format( "{0:MM}", DateTime.Now)
strDate = strDate & String.Format( "{0d}", DateTime.Now)
Using sw As StreamWriter = New StreamWriter("C:\SSIS\I_" & strDate & "_" & recordcount & ".txt")
sw.WriteLine(sb.ToString)
sw.Close()
End Using
'this will create a text file in bin directory
End Sub
End Class
This is how you generate tab delimited files to test check Use Script Tas from SSIS
|||Uh, okay. Good. So why even use SSIS then? Write your own program as you have done, compile it, and execute the resulting binary file. Leave the bloat of SSIS out of it.|||
I am going to put this piece in middle of my design.
It was the one of the key to finish my jik-so-puzzle.
Other one is to delete the files from Unix Aix Server with FTP connection.
And here too I have to go all the way round, as there FTP connection works fine on Microsoft server but not on UNIX server.
It does not allow you to delete the files on UNIX server.
That is the reason I am using the code in SSIS.
