Dynamically create text file as destination from sql script in SSIS

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.