Search Results

Search found 936 results on 38 pages for 'flat'.

Page 1/38 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• Parsing flat files using SSIS : SSIS Nugget

  - by jamiet

  Often when using SQL Server Integration Services (SSIS) you will find there is more than one way of accomplishing a task and that the most obvious method of doing so might not be the optimal one. In the video below I demonstrate this by way of an experiment using SSIS’s Flat File Source component; I show different ways that you can pull data from a flat file into the SSIS dataflow and also how the nature of the data itself can influence your choice as to how this task should be accomplished. If you are having trouble viewing the video in your blog reader then head to http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/25/parsing-flat-files-using-ssis-ssis-nugget.aspx to see it as it is hosted on my blog!  The main point I want to get across from this video is that a little bit of creative thinking when building your dataflows can sometimes be very beneficial for performance; quite often building a solution that isn’t the most obvious might actually turn out to be the best one. You’ll notice, if you have watched the video, that my editing skills weren’t quite up to snuff and I cut off the final few words however all I was saying was that if you have any feedback on this video then I would love to hear it either via email or preferably the comments section below. I hope this turns out to be useful to some of you. @Jamiet P.S. Incidentally the parsing that we do using SSIS expressions in the video would be much easier if we had a TOKENISE function in SSIS’s expression language and I have asked for the introduction of such a function on Connect at [SSIS] TOKEN(string, tokeniser_string, occurence) function. Feel free to go and vote that up if you think this feature would be useful! Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

  - by jamiet

  I saw a question on MSDN’s SSIS forum the other day that went something like this: I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that? I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain! The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties: You’ll see in the screenshot above that I have set FileNameColumnName=“Filename” (it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot: So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

  - by jamiet

  I saw a question on MSDN’s SSIS forum the other day that went something like this: I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that? I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain! The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties: You’ll see in the screenshot above that I have set FileNameColumnName=“Filename” (it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot: So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there. @Jamiet Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• BizTalk 2009 - Error when Testing Map with Flat File Source Schema

  - by StuartBrierley

  I have recently been creating some flat file schemas using the BizTalk Server 2009 Flat File Schema Wizard.  I have then been mapping these flat file schemas to a "normal" xml schema format. I have not previsouly had any cause to map flat files and ran into some trouble when testing the first of these flat file maps; with an instance of the flat file as the source it threw an XSL transform error: Test Map.btm: error btm1050: XSL transform error: Unable to write output instance to the following <file:///C:\Documents and Settings\sbrierley\Local Settings\Temp\_MapData\Test Mapping\Test Map_output.xml>. Data at the root level is invalid. Line 1, position 1. Due to the complexity of the map in question I decided to created a small test map using the same source and destination schemas to see if I could pinpoint the problem.  Although the source message instance vaildated correctly against the flat file schema, when I then tested this simplified map I got the same error. After a time of fruitless head scratching and some serious google time I figured out what the problem was. Looking at the map properties I noticed that I had the test map input set to "XML" - for a flat file instance this should be set to "native".

  Read the article

• Biztalk Flat File Schema - how to accept a LF or CRLF as the line delimiter

  - by FullOfQuestions

  Hi, Our client sends us a flat file as input, which we then take and convert to an XML file before sending to the destination system. The flat file consists of multiple lines, each line is delimited by LF or CRLF. How do I create a Flat File Schema so that Biztalk can interpret each line of data regardless of whether the line was delimited by LF (0x0A) or CRLF (0x0D 0x0A)? Thank you in advance, M

  Read the article

• Flat File Connection Manager in SSIS package shows "Valid File Name Must be Selected"

  - by Traples

  (Flat File Location) Samba Share | Windows Share (SSIS) _______________________________ | | XP 32bit | Works | Works | | 2003 Serv 32bit | Works | Works | | Vista 64bit | ERROR | Works | | Win 7 64bit | ERROR | Works | | 2008 Serv 64bit | ERROR | Works I created an SSIS package in VS 2008 that parses a flat file from a shared folder and puts the records into a SQL Server db. I recently installed Windows 7 and VS 2008 on a new workstation. When I import the package from TFS and open it, I get the error Validation error. Parse and Import Catalog Flat File: MySSISPackage: The file name "\\shared\flatfile.txt" specified in the connection was not valid. When I open the Flat File Connection Manager Editor, there is an error stating: A valid file name must be selected I can browse to and select the file from inside the editor, but I cannot change any properties, or move away from the General tab because of this error. If I go back to my laptop (Windows XP), where the package was first created, there is no error. Both workstations are on the same domain, and I'm logging in using the same credentials. Any ideas as to why I would receive this error from one workstation and not another? UPDATE: If I take the .dtsx package from the running workstation and load it into SSIS on the server, I get the following errors when it tries to run: Error: The file name "\\shared\flatfile.txt" specified in the connection was not valid. and... Error: Connection "MySSISPackage" failed validation. and... Error: The file name property is not valid. The file name is a device or contains invalid characters. UPDATE 2: a) The Shared folder I'm trying to pull the flat file from is a Samba share on a Unix box. b) If I access the file using SSIS on any 64-bit platform (Windows 7 64-bit, Vista 64-bit, Windows Server 2008) I get the error "A valid file name must be selected." c) Accessing the file using SSIS from 32-bit environments (Windows XP 32-bit, Windows Server 2003 32-bit) there is no problem. d) If I move the file to a shared folder on a Windows server, 64-bit SSIS recognizes the file.

  Read the article

• Algorithm for parsing a flat tree into a non-flat tree

  - by Chad Johnson

  I have the following flat tree: id name parent_id is_directory =========================================================== 50 app 0 1 31 controllers 50 1 11 application_controller.rb 31 0 46 models 50 1 12 test_controller.rb 31 0 31 test.rb 46 0 and I am trying to figure out an algorithm for getting this into the following tree structuree: [{ id: 50, name: app, is_directory: true children: [{ id: 31, name: controllers, is_directory: true, children: [{ id: 11, name: application_controller.rb is_directory: false },{ id: 12, name: test_controller.rb, is_directory: false }], },{ id: 46, name: models, is_directory: true, children: [{ id: 31, name: test.rb, is_directory: false }] }] }] Can someone point me in the right direction? I'm looking for steps (eg. build an associative array; loop through the array looking for x; etc.).

  Read the article

• What arguments to use to explain why SQL Server is far better then a flat file

  - by jamone

  The higher ups in my company were told by good friends that flat files are the way to go, and we should switch from SQL Server to them for everything we do. We have over 300 servers and hundreds of different databases. From just the few I'm involved with we have 10 billion records in quite a few of them with upwards of 100k new records a day and who knows how many updates... Me and a couple others need to come up with a response saying why we shouldn't do this. Most of our stuff is ASP.NET with some legacy ASP. We thought that making a simple console app that tests/times the same interactions between a flat file (stored on the network) and SQL over the network doing large inserts, searches, updates etc along with things like network disconnects randomly. This would show them how bad flat files can be especially when you are dealing with millions of records. What things should I use in my response? What should I do with my demo code to illustrate this? My sort list so far: Security Concurrent access Performance with large amounts of data Amount of time to do such a massive rewrite/switch Lack of transactions PITA to map relational data to flat files NTFS doesn't support tons of files in a directory well I fear that this will be a great post on the Daily WTF someday if I can't stop it now.

  Read the article

• where does the term flat-file come from?

  - by Igor

  I was wondering if anyone knows. Is 'flat' the opposite of hierarchal? Does it come from a phrase like, "this is a flat-out regular file"?

  Read the article

• who convert flat file xml in biztalk server?

  - by Anish

  Hi, I am new to BizTalk server. So if this question is so obvious, plz forgive :( I have a flat file from one application, which i have to send to a biztalk server. In that case which component in my biztalk server converts my flat file to xml. Also I heard that I have to create an input schema(.xsd file), why i need an input message schema? Thanks in advance!

  Read the article

• Accessing a vector<vector<int>> as a flat array

  - by user1762276

  For this array: vector<vector<int> > v; v.push_back(vector<int>(0)); v.back().push_back(1); v.back().push_back(2); v.back().push_back(3); v.back().push_back(4); I can output {1, 2, 3, 4} easily enough: cout << v[0][0] << endl; cout << v[0][1] << endl; cout << v[0][2] << endl; cout << v[0][3] << endl; To access it as a flat array I can do this: int* z = (int*)&v[0].front(); cout << z[0] << endl; cout << z[1] << endl; cout << z[2] << endl; cout << z[3] << endl; Now, how do I access the multidimensional vector as a flat multidimensional array? I cannot use the same format as accessing a single-dimensional vector: // This does not work (outputs garbage) int** n = (int**)&v.front(); cout << n[0][0] << endl; cout << n[0][1] << endl; cout << n[0][2] << endl; cout << n[0][3] << endl; The workaround I've found is to do this: int** n = new int* [v.size()]; for (size_t i = 0; i < v.size(); i++) { n[i] = &v.at(i).front(); } cout << n[0][0] << endl; cout << n[0][1] << endl; cout << n[0][2] << endl; cout << n[0][3] << endl; Is there a way to access the entire multidimensional vector like a flat c-style array without having to dynamically allocate each dimension above the data before accessing it? Speed is not critical in the implementation and clarity for maintenance is paramount. A multidimensional vector is just fine for storing the data. However, I want to also expose the data as a flat c-style array in the SDK so that it can be easily accessible by other languages. This means that exposing the vectors as an STL object is a no go. The solution I came up with works fine for my needs as I only evaluate the array once at the very end of processing to "flatten" it. However, is there a better way to go about this? Or am I already doing it the best way I possibly can without re-implementing my own data structure (overkill since my flatten code is only a few lines). Thank you for your advice, friends!

  Read the article

• Algorithm for converting hierarchical flat data (w/ ParentID) into sorted flat list w/ indentation l

  - by eagle

  I have the following structure: MyClass { guid ID guid ParentID string Name } I'd like to create an array which contains the elements in the order they should be displayed in a hierarchy (e.g. according to their "left" values), as well as a hash which maps the guid to the indentation level. For example: ID Name ParentID ------------------------ 1 Cats 2 2 Animal NULL 3 Tiger 1 4 Book NULL 5 Airplane NULL This would essentially produce the following objects: // Array is an array of all the elements sorted by the way you would see them in a fully expanded tree Array[0] = "Airplane" Array[1] = "Animal" Array[2] = "Cats" Array[3] = "Tiger" Array[4] = "Book" // IndentationLevel is a hash of GUIDs to IndentationLevels. IndentationLevel["1"] = 1 IndentationLevel["2"] = 0 IndentationLevel["3"] = 2 IndentationLevel["4"] = 0 IndentationLevel["5"] = 0 For clarity, this is what the hierarchy looks like: Airplane Animal Cats Tiger Book I'd like to iterate through the items the least amount of times possible. I also don't want to create a hierarchical data structure. I'd prefer to use arrays, hashes, stacks, or queues. The two objectives are: Store a hash of the ID to the indentation level. Sort the list that holds all the objects according to their left values. When I get the list of elements, they are in no particular order. Siblings should be ordered by their Name property. Update: This may seem like I haven't tried coming up with a solution myself and simply want others to do the work for me. However, I have tried coming up with three different solutions, and I've gotten stuck on each. One reason might be that I've tried to avoid recursion (maybe wrongly so). I'm not posting the partial solutions I have so far since they are incorrect and may badly influence the solutions of others.

  Read the article

• database vs flat file, which is a faster structure for regex matching with many simultaneous request

  - by Jamex

  Hi, which structure returns faster result and/or less taxing on the host server, flat file or database (mysql)? Assume many users (100 users) are simultaneously query the file/db. Searches involve pattern matching using regex against a static file/db. File has 50,000 unique lines (same data type). There could be many matches. There is no writing to the file/db, just read. Is it possible to have a duplicate the file/db and write a logic switch to use the backup file/db if the main file is in use? Which language is best for the type of structure? Perl for flat and PHP for db? TIA

  Read the article

• database vs flat file, which is a faster structure for "regex" matching with many simultaneous reque

  - by Jamex

  Hi, which structure returns faster result and/or less taxing on the host server, flat file or database (mysql)? Assume many users (100 users) are simultaneously query the file/db. Searches involve pattern matching against a static file/db. File has 50,000 unique lines (same data type). There could be many matches. There is no writing to the file/db, just read. Is it possible to have a duplicate the file/db and write a logic switch to use the backup file/db if the main file is in use? Which language is best for the type of structure? Perl for flat and PHP for db? Addition info: If I want to find all the cities have the pattern "cis" in their names. Which is better/faster, using regex or string functions? Please recommend a strategy TIA

  Read the article

• flat-rate backup service for Windows Server

  - by Colin Pickard

  Hi, Does anyone know if there is a decent flat-rate backup service which supports Windows Server? I've investigated the following: Backblaze - no WS support, sales say they have a "no server" policy JungleDisk - not flat rate Mozy - no WS on regular edition, no flat-rate on Pro edition Dropbox - no flat rate Carbonite - technically flat rate, but throttles uploads to modem speeds EDIT: Very similar question: Is there a decent flat-rate online backup solution for Linux machines?

  Read the article

• Good conventions for embedding schema of a flat file

  - by Ville Koskinen

  We receive lots of data as flat files: delimitted or just fixed length records. It's sometimes hard to find out what the files actually contain. Are there any well established practices for embedding the schema of the file to the beginning or the end of a file to make the file self-explanatory? Just to get an idea, imagine something like this: <data name=test records=2 type=fixed> <field name=foo start=0 length=2 type=numeric> <field name=bar start=2 length=4 type=text> </data> 11test 12ing We would parse the xml in the beginning and use it for reading the records.

  Read the article

• Simple, fast SQL queries for flat files.

  - by plinehan

  Does anyone know of any tools to provide simple, fast queries of flat files using a SQL-like declarative query language? I'd rather not pay the overhead of loading the file into a DB since the input data is typically thrown out almost immediately after the query is run. Consider the data file, "animals.txt": dog 15 cat 20 dog 10 cat 30 dog 5 cat 40 Suppose I want to extract the highest value for each unique animal. I would like to write something like: cat animals.txt | foo "select $1, max(convert($2 using decimal)) group by $1" I can get nearly the same result using sort: cat animals.txt | sort -t " " -k1,1 -k2,2nr And I can always drop into awk from there, but this all feels a bit awkward (couldn't resist) when a SQL-like language would seem to solve the problem so cleanly. I've considered writing a wrapper for SQLite that would automatically create a table based on the input data, and I've looked into using Hive in single-processor mode, but I can't help but feel this problem has been solved before. Am I missing something? Is this functionality already implemented by another standard tool? Halp!

  Read the article

• find and replace values in a flat-file using PHP

  - by peirix

  I'd think there was a question on this already, but I can't find one. Maybe the solution is too easy... Anyway, I have a flat-file and want to let the user change the values based on a name. I've already sorted out creating new name+value-pairs using the fopen('a') mode, using jQuery to send the AJAX call with newValue and newName. But say the content looks like this: host|http:www.stackoverflow.com folder|/questions/ folder2|/users/ And now I want to change the folder value. So I'll send in folder as oldName and /tags/ as newValue. What's the best way to overwrite the value? The order in the list doesn't matter, and the name will always be on the left, followed by a |(pipe), the value and then a new-line. My first thought was to read the list, store it in an array, search all the [0]'s for oldName, then change the [1] that belongs to it, and then write it back to a file. But I feel there is a better way around this? Any ideas? Maybe regex?

  Read the article

• create fixed length flat file with Java

  - by Leslie

  I have a process that currently runs in a Delphi application that I wrote and I need to convert it to a Java process that will run on our web application. Basically our State Financial (legacy) system requires this file in a specific output. In Delphi it is like this: procedure CreateSHAREJournalFile(AppDate : string; ClassCode : string; BudgetRef : String; AccountNumber : string; FYEStep : integer); var GLFileInfo : TStrings; MPayFormat, HPayFormat, TPayFormat : string;<br> const<br> //this is the fixed length format for each item in the file<br> HeaderFormat = '%-1s%-5s%-10s%-8s%-12s%-10s%-21s%-3s%-71s%-3s%-20s%-1s';<br> DetailFormat = '%-1s%-5s%-9s%-10s%-10s%-10s%-10s%-8s%-6s%-5s%-5s%-5s%-8s%-25s%-10s%-60s%-28s%-66s%-28s';<br> begin<br> try<br>//get the data from the query<br> with dmJMS.qryShare do<br> begin<br> SQL.Clear;<br> SQL.Add('SELECT SUM(TOTHRPAY) As HourPay, SUM(TOTMLPAY) As MilePay, SUM(TOTALPAY) AS TotalPay FROM JMPCHECK INNER JOIN JMPMAIN ON JMPCHECK.JURNUM = JMPMAIN.JURNUM WHERE PANELID LIKE ''' + Copy(AppDate, 3, 6) + '%'' ');<br> if FYEStep > -1 then<br> SQL.Add('AND WARRANTNO = ' + QUotedStr(IntToStr(FYEStep)));<br> Active := True;<br> //assign totals to variables so they can be padded with leading zeros<br> MPayFormat := FieldByName('MilePay').AsString;<br> while length(MPayFormat) < 28 do <br>MPayFormat := '0' + MPayFormat;<br> HPayFormat := FieldByName('HourPay').AsString;<br> while length(HPayFormat) < 28 do <br>HPayFormat := '0' + HPayFormat;<br> TPayFormat := Format('%f' ,[(FieldByName('TotalPay').AsCurrency)]);<br> while length(TPayFormat) < 27 do<br> TPayFormat := '0' + TPayFormat;<br> TPayFormat := '-' + TPayFormat;<br> //create a TStringlist to put each line item into<br> GLFileInfo := TStringList.Create;<br> //add header info using HeaderFormat defined above<br> GLFileInfo.Add(Format(HeaderFormat, ['H', '21801', 'NEXT', FormatDateTime('MMDDYYYY', Today), '', 'ACTUALS', '', 'EXT', '', 'EXT', '', 'N']));<br> //add detail info using DetailFormat defined above<br> GLFileInfo.Add(Format(DetailFormat, ['L', '21801', '1', 'ACTUALS', AccountNumber, '', '1414000000', '111500', '', '01200', ClassCode, '', BudgetRef, '', AccountNumber + '0300', '', MPayFormat, '', MPayFormat]));<br> GLFileInfo.Add(Format(DetailFormat, ['L', '21801', '2', 'ACTUALS', AccountNumber, '', '1414000000', '111500', '', '01200', ClassCode, '', BudgetRef, '', AccountNumber + '0100', '', HPayFormat, '', HPayFormat]));<br> GLFileInfo.Add(Format(DetailFormat, ['L', '21801', '3', 'ACTUALS', '101900', '', '1414000000', '111500', '', '01200', ClassCode, '', BudgetRef, '', '', '', TPayFormat, '', TPayFormat]));<br> //save TStringList to text file<br> GLFileINfo.SaveToFile(ExtractFilePath(Application.ExeName) + 'FileTransfer\GL_' + formatdateTime('mmddyy', Today) + SequenceID + '24400' + '.txt');<br> end;<br> finally<br> GLFileINfo.Free;<br> end; end; is there an equivalent in Java for the Format option? Or the TStringList that saves to a text file? Thanks for any information....haven't done a lot of Java programming! Leslie

  Read the article

• SSIS parsing of an irregular flat file?

  - by ElHaix

  I'm pretty familiar with SSIS parsing of regular delimited text data files, however, I'm looking for some advice on an approach to tackle a file that looks like this test file: ISA*00* *00* *01*220220220 *ZZ*RL CODE 01*060327*1212*U*00300*000008859*0*P*:~ GS*RA*CPA-BPT*LOCALUTILITY*060319*1212*970819003*X*003030~ ST*820*000000001~ BPR*C*321.91*C*X12*CBC*04*000300488**9918939***04*000300002**1598564*070319~ TRN*1*00075319970819105029~ REF*RR*0003199708190000174858~ DTM*097*070318~ DTM*107*070318~ N1*PR*DIRECT PAYMENT~ N1*PE*ABC CORPORATE BILLER*ZZ*90005836~ ENT*1~ N1*PR*BILLING - TEST - NATTRASS~ RMR*CR*0009381082105011**142.15~ REF*TN*000303965~ DTM*109*070316~ ENT*2~ N1*PR*BILL FREID TEST~ RMR*CR*0011010451800011**179.76~ REF*TN*000304189~ The 321.91 is the total of the transaction. I would prefer to do this with SSIS, but could also do create a C# parser. Suggestions would be appreciated. Thank you.

  Read the article

• android SQLite vs Flat Files

  - by mixm

  hi. im creating a game right now and im a bit stuck on how to implement storage of levels. i need to be able to download level files from the internet ota. im not so familiar with transferring files ota, but i have some experience with databases (mysql). what would be a better way of storing the game's level data?

  Read the article

• How do I get SSIS Data Flow to put '0.00' in a flat file?

  - by theog

  I have an SSIS package with a Data Flow that takes an ADO.NET data source (just a small table), executes a select * query, and outputs the query results to a flat file (I've also tried just pulling the whole table and not using a SQL select). The problem is that the data source pulls a column that is a Money datatype, and if the value is not zero, it comes into the text flat file just fine (like '123.45'), but when the value is zero, it shows up in the destination flat file as '.00'. I need to know how to get the leading zero back into the flat file. I've tried various datatypes for the output (in the Flat File Connection Manager), including currency and string, but this seems to have no effect. I've tried a case statement in my select, like this: CASE WHEN columnValue = 0 THEN '0.00' ELSE columnValue END (still results in '.00') I've tried variations on that like this: CASE WHEN columnValue = 0 THEN convert(decimal(12,2), '0.00') ELSE convert(decimal(12,2), columnValue) END (Still results in '.00') and: CASE WHEN columnValue = 0 THEN convert(money, '0.00') ELSE convert(money, columnValue) END (results in '.0000000000000000000') This silly little issue is killin' me. Can anybody tell me how to get a zero Money datatype database value into a flat file as '0.00'?

  Read the article

• SSIS Technique to Remove/Skip Trailer and/or Bad Data Row in a Flat File

  - by Compudicted

  I noticed that the question on how to skip or bypass a trailer record or a badly formatted/empty row in a SSIS package keeps coming back on the MSDN SSIS Forum. I tried to figure out the reason why and after an extensive search inside the forum and outside it on the entire Web (using several search engines) I indeed found that it seems even thought there is a number of posts and articles on the topic none of them are employing the simplest and the most efficient technique. When I say efficient I mean the shortest time to solution for the fellow developers. OK, enough talk. Let’s face the problem: Typically a flat file (e.g. a comma delimited/CSV) needs to be processed (loaded into a database in most cases really). Oftentimes, such an input file is produced by some sort of an out of control, 3-rd party solution and would come in with some garbage characters and/or even malformed/miss-formatted rows. One such example could be this imaginary file: As you can see several rows have no data and there is an occasional garbage character (1, in this example on row #7). Our task is to produce a clean file that will only capture the meaningful data rows. As an aside, our output/target may be a database table, but for the purpose of this exercise we will simply re-format the source. Let’s outline our course of action to start off: Will use SSIS 2005 to create a DFT; The DFT will use a Flat File Source to our input [bad] flat file; We will use a Conditional Split to process the bad input file; and finally Dump the resulting data to a new [clean] file. Well, only four steps, let’s see if it is too much of work. 1: Start the BIDS and add a DFT to the Control Flow designer (I named it Process Dirty File DFT): 2, and 3: I had added the data viewer to just see what I am getting, alas, surprisingly the data issues were not seen it:   What really is the key in the approach it is to properly set the Conditional Split Transformation. Visually it is: and specifically its SSIS Expression LEN([After CS Column 0]) > 1 The point is to employ the right Boolean expression (yes, the Conditional Split accepts only Boolean conditions). For the sake of this post I re-named the Output Name “No Empty Rows”, but by default it will be named Case 1 (remember to drag your first column into the expression area)! You can close your Conditional Split now. The next part will be crucial – consuming the output of our Conditional Split. Last step - #4: Add a Flat File Destination or any other one you need. Click on the Conditional Split and choose the green arrow to drop onto the target. When you do so make sure you choose the No Empty Rows output and NOT the Conditional Split Default Output. Make the necessary mappings. At this point your package must look like: As the last step will run our package to examine the produced output file. F5: and… it looks great!

  Read the article

• SSIS - Can I get the column schema for a flat file source from a database?

  - by Steve Clement

  We receive a nightly data export from a vendor in the form of about 10 tab-delimited flat file without column headers. In addition, the vendor provides us with the SQL scripts for the database tables so that we can import the files into our system. Unfortunately, the vendor recently changed the schema for the flat files. Each file has upwards 150 columns, and having to go through the DB schema and adjust column types on a Flat File Data Source in SSIS is extremely time consuming, not to mention a royal pain. Since I know the file data layout in the database schema, is there any way I can dynamically pull that into a Flat File source to set the columns correctly? Or am I just stuck with manually setting everything?

  Read the article

• What arguments to use to explain why a SQL DB is far better then a flat file

  - by jamone

  The higher ups in my company were told by good friends that flat files are the way to go, and we should switch from MS SQL server to them for everything we do. We have over 300 servers and hundreds of different databases. From just the few I'm involved with we have 10 billion records in quite a few of them with upwards of 100k new records a day and who knows how many updates... Me and a couple others need to come up with a response saying why we shouldn't do this. Most of our stuff is ASP.NET with some legacy ASP. We thought that making a simple console app that tests/times the same interactions between a flat file (stored on the network) and SQL over the network doing large inserts, searches, updates etc along with things like network disconnects randomly. This would show them how bad flat files can be espically when you are dealing with millions of records. What things should I use in my response? What should I do with my demo code to illustrate this? My sort list so far: Security Concurent access Performance with large ammounts of data Ammount of time to do such a massive rewrite/switch Lack of transactions PITA to map relational data to flat files I fear that this will be a great post on the Daily WTF someday if I can't stop it now.

  Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >