Hi, I have a simple flat file with data as tablename,column1,column2 tablename,col1,col2,col3. Different number of columns. I need to extract data from the specified table and its corresponding columns , as given in the flat file. Can somebody suggest me the best approach?MSBI Developer

if you mean to say you have multiple tables in the same flat file then you can use a script task to extract the data and then transfer the records. but you cant use a flat file connection in a dataflow task-------------------------------------------------------- Surender Singh Bhadauria

The best way to read unstructured flat files is get whole row in single column and treat it in a script component There is a example here http://geekswithblogs.net/evankoch/archive/2007/09/19/115450.aspx and here http://consultingblogs.emc.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky

Are the datatypes for each row the same? If so you could use a Script Component as a source. Here is an example: http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source.html This: "test1";"abc";"xyz";"123" "test2";"cba";"zyx";"321" "test3";"abc" "test4";"efg";"zyx" "test5";"cba";"zyx";"321" becomes: Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Hi, The datatypes are the same. The flat file contains only the table names and its column names. I need to extract them, assign it to user defined variables and generate a dynamic sql query to get the data from that particular table. I need the right approach to do this. Please let me know if i am not clear.MSBI Developer

Hi, The datatypes are the same. The flat file contains only the table names and its column names. I need to extract them, assign it to user defined variables and generate a dynamic sql query to get the data from that particular table. I need the right approach to do this. Please let me know if i am not clear. MSBI Developer Dynamic mappings are tricky... what's the destination of those dynamic sql queries? Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Right now yes. I keep thinking that you should read each whole row in a column and generate each query in a Script component. After the queries being generated insert into DataSet destination and uses a foreach loop to read this dataset and use as source query in another dataflow. I mean? Víctor M. Sánchez García (ES) (BI) Hope this help. Please vote if you find this posting was helpful. if this is an answer to your question, please mark it. http://bifase.blogspot.com | http://twitter.com/atharky

Hi, @SSISJoost: Yes we are stuck at that dynamic mapping to variables :-(. The destination is a flat file per table. @Victor M : I got your idea. Can you suggest some sample Script on this?MSBI Developer

This is very simple if you use a script task. First you will want to create the script task and depending on your preference (VB.NET, C# default) choose the correct type of coding you like best. Once you are in the file you create all the variables you want to use. Here is a code example to get you started. This particular script creates a file connection, reads the entire file into a stream, and then writes it out to a separate file preserving the original data. I your case you will want to create a loop to break once you find each table name. Here are 2 separate examples, one just shows how to read the file, write extra values to the file other than what was read to a different location. Something like The second example comes from: http://www.mssqltips.com/tip.asp?tip=2395 where performance counters are read in from a file and certain values are discarded. While strYourStringNameHere Not Table2 Write to Table1.txt Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Text Imports System.IO <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _ <System.CLSCompliantAttribute(False)> _ 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() ' Dim FullPath As String Dim strWritePath As String Dim ErrInfo As String Dim strContents As String Dim objReader As StreamReader Dim objWriter As StreamWriter Dim FileNumber As Int32 Dim strxmlDec As String = CType(Dts.Variables("strXML10Head").Value, String) Dim strxmlSchema As String = CType(Dts.Variables("strXMLSchemaHeader").Value, String) Dim strCloseTag As String = CType(Dts.Variables("strXMLClosingTag").Value, String) Try If Dts.Variables.Contains("intSetNumber") = True Then FileNumber = CType(Dts.Variables("intSetNumber").Value, Int32) FullPath = "C:\Exports\xml" & FileNumber.ToString & ".xml" strWritePath = "C:\Exports\xmlOut" & FileNumber.ToString & ".xml" objReader = New StreamReader(FullPath) strContents = objReader.ReadToEnd() strContents.Replace(" ", "") strContents = strxmlDec & vbCrLf & strxmlSchema & strContents & vbCrLf & strCloseTag objWriter = New StreamWriter(strWritePath) objWriter.Write(strContents) objWriter.Close() objReader.Close() End If Catch Ex As Exception ErrInfo = Ex.Message End Try ' Dts.TaskResult = ScriptResults.Success End Sub End Class

''''***************************************************************** ''''''Second example begins here ***************************'''''' Public Overrides Sub CreateNewOutputRows() Dim InputFile As String Dim RowBuffer As String = Nothing Dim Done As Boolean = False Dim RawColumnNames As String() Dim ColumnNames As New List(Of String) Dim ColumnName As String Dim CharsToTrim() As Char = {""""c, " "c} Dim ServerName As String = Nothing Dim FullColumnName As String InputFile = Variables.InputFile Using s As StreamReader = New StreamReader(InputFile) RowBuffer = s.ReadLine() If RowBuffer IsNot Nothing Then Dim ServerNameEndIndex As Integer Dim RawServerName As String = Nothing ' Get column names from first row RawColumnNames = RowBuffer.Split(New [Char]() {","c}) For Each RawColumnName As String In RawColumnNames ' strip off any double quotes FullColumnName = RawColumnName.Trim(CharsToTrim) ' Get the server name prepended to each counter; ' e.g. \\SERVERNAME\counter name If ServerName Is Nothing Then If FullColumnName.StartsWith("\\") Then ServerNameEndIndex = FullColumnName.IndexOf("\", 2) If ServerNameEndIndex <> -1 Then ServerName = FullColumnName.Substring(2, ServerNameEndIndex - 2) RawServerName = "\\" + ServerName + "\" End If End If End If ' Get the counter name; strip off the server name If RawServerName IsNot Nothing Then If FullColumnName.StartsWith(RawServerName) Then ColumnName = FullColumnName.Substring(ServerNameEndIndex + 1) Else ColumnName = FullColumnName End If Else ColumnName = FullColumnName End If ColumnNames.Add(ColumnName) Next Dim ColumnNumber As Integer = 0 Dim DateTimeString As String = Nothing Dim RawColumnValues As String() Dim CounterValue As Double Dim ColumnValue As String Dim CaptureDate As Date Dim CaptureTime As String Dim CaptureHourOfDay As Integer Dim CaptureTimeBlankIndex As Integer ' iterate thru each row While Done = False ColumnNumber = 0 RowBuffer = s.ReadLine() If RowBuffer IsNot Nothing Then RawColumnValues = RowBuffer.Split(New [Char]() {","c}) For Each RawColumnValue As String In RawColumnValues ' strip the double quotes ColumnValue = RawColumnValue.Trim(CharsToTrim) If ColumnNumber = 0 Then ' Parse the datetime column; format is: MM/DD/YYYY HH:MI:SS.999 DateTimeString = ColumnValue CaptureTimeBlankIndex = ColumnValue.IndexOf(" ") Date.TryParse(ColumnValue.Substring(0, CaptureTimeBlankIndex), _ CaptureDate) CaptureTime = ColumnValue.Substring(CaptureTimeBlankIndex + 1) Integer.TryParse(CaptureTime.Substring(0, _ CaptureTime.IndexOf(":")), CaptureHourOfDay) Else If String.IsNullOrEmpty(ColumnValue) = False Then If Double.TryParse(ColumnValue, CounterValue) Then OutputBuffer.AddRow() OutputBuffer.CounterValue = CounterValue OutputBuffer.CaptureTimestamp = DateTimeString OutputBuffer.CounterName = ColumnNames(ColumnNumber) OutputBuffer.InputFile = InputFile OutputBuffer.ServerName = ServerName OutputBuffer.CaptureDate = CaptureDate OutputBuffer.CaptureTime = CaptureTime OutputBuffer.CaptureHourOfDay = CaptureHourOfDay End If End If End If ColumnNumber += 1 Next Else Done = True End If End While End If End Using End Sub

Hi, @SSISJoost: Yes we are stuck at that dynamic mapping to variables :-(. The destination is a flat file per table. @Victor M : I got your idea. Can you suggest some sample Script on this? MSBI Developer Hope you know how to write .net - You could read the rows in a Script Task and split each row on the comma (see Script Component example in the link) - Create a query bases on those columns - Execute the query with an ole db command (OleDbDataReader) - Write the reader to an flat file.... So basicly.. you're writing a .Net application in a SSIS Script Task... not sure you need SSIS for it....Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com