Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
on7june
Starting Member
41 Posts |
 Posted - 2008-02-21 : 11:34:59
|
| Hi, My need is to load data from flat files to SQL tables. All the flat files are different. For convenience flat file name and table name is similar. I have placed a For loop container and in which I used SCRIPT TASK to read the file names from the specified folder and i populated this value to SSIS variable. All my flat file name and destination table name are given using this Script task. For the first run it executes perfectly, but for the second run it fails. The reason is due to the flat file connection manager. The file name is changed since it is taken care by my script, but the columns are not mapped it still have the previous file's columns. I do not know how to solve this.Please tell me if there is any other way to solve this or i need to refine my process.Actually i have more than 100 files and so i am trying to do it at one short rather than creating individual packages.Sarvan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-21 : 12:12:56
|
| I dont think you can change the metadata of flat file at runtime. |
 |
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-21 : 15:58:56
|
| You cannot change the metadata at runtime. You can use the for loop container, but you will need to create a different dataflow for each file format. |
|
|
|
on7june
Starting Member
41 Posts |
Posted - 2008-02-22 : 01:13:41
|
| Thanks for your kind response. So what should i do now, do i need to create 100 data flow task every flat files.Sarvan |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-22 : 15:41:44
|
There is one way that you can do it, but it will involve extra code to split the results at the end. First, this method relies on the ROW and COLUMN delimiters being the SAME FOR EVERY FILE. If you have files with different delimiters, make a separate dataflow for each DELIMETER TYPE. The solution is to create a flat file connection manager that has something crazy as the column delimeter that will NEVER appear in your file like "~!@#$%^&*()_+". SSIS will read each line into one column (make sure you make it a string 8000 or something). Then, make a script task that has as many output columns as your file with the most columns. Name them Column1, Column2, etc. Your script task would look like:Public Class ScriptMain Inherits UserComponent Private buff As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim arrDataRow As String() = Row.Column0.Split("|".ToCharArray()) Dim iMax As Integer = arrDataRow.Length - 1 If buff.ColumnCount - 2 < iMax Then iMax = buff.ColumnCount - 2 For i As Integer = 0 To iMax buff.SetString((buff.ColumnCount - 2) - i, arrDataRow(i)) Next End Sub Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer) buff = Buffer MyBase.ProcessInput(InputID, Buffer) End SubEnd ClassNext, you would add a derived column and add the filename or destination table. The split columns along w/ the filename/tablename identifier would be pushed into a raw table. From there you could import to whatever table you needed via T-SQL. |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-22 : 16:47:30
|
| I need to make a correction! Instead of using the Format of Delimited in the Flat File Connection Manager, use Ragged Right and then in the Advanced area, set the length of column0 to 8000. It will work the way I had it, but Ragged Right is the CORRECT way to do it. ;) |
|
|
|
|
|
|
|
|
Change the mappings in SSIS packages