I have spent many days trying to complete an SSIS program to load data from a CSV file into a database table. I have read all of the MS documentation, all of the samples I can find, and I have seen many other users post this exact question on this forum. Frankly, I think the new SSIS object model is non-intuitive and poorly documented. That would be tolerable if there were useful examples. Instead, I have found nearly 20 examples of other developers posting my exact question. There is never an answer. Microsoft needs to show the code required to load a CSV file with an arbitrary number of columns into a SQL Server database table (where the source has no column headers, or the column headers dont match the column names in the database table). I don't need to see any fancy transformations. What I do need to see is how the columns are mapped from the CSV FlatFileSource into the OleDBDestination. Further, it would be very useful if there were some explanation attached to the code so it appeared less like black magic and more like something useful in the real world. I can create the connection managers, the tasks, the data sources, and all of the other objects. I can connect them all together. However, I cannot map the columns and I cannot find a single useful example or document explaining the details of this process. If anyone knows how to do this and has actually implemented and tested working code, I would really love to see it. Please save useless remarks such as "Post a more specific question" for someone else. This is a specific question and it is a question asked by many users on this forum. Please post a specific answer.

YADeveloper, First off, your post seems a bit hostile. Remember, this is a user driven forum and most of the help you willget here is fromthe community and not microsoft employees (although they do help out as much as possible). The reason that this type of process is not a simple drag and drop operation is that SSIS and SQL 2005 are much more meta data oriented. You will not be allowed to import something for which the pipeline is unaware how to handle properly. Now, there are going to be a couple of differentthat you can (hopefully) use. For instance: If there are no data transformations needed, you can use a bulk insert task and pass parameters -- NOTE: Sounds like the best solution for you. (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1383427&SiteID=17 http://msdn2.microsoft.com/en-us/library/ms141239.aspx http://msdn2.microsoft.com/en-us/library/ms188365.aspx) you can load everything from the csv into one column and use a script component to break it into individual pieces, which can then hopefully be imported -- NOTE: this probably will not work unless if you know exactly what you want to import, which, it sounds like you are not going to know at the time you are building your packages.(http://blogs.conchango.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx http://agilebi.com/cs/blogs/jwelch/archive/2007/05/16/handling-varying-columns-part-2.aspx) you can build your package programmatically to load the data for each package individually -- NOTE: You will still need to know what you are trying to import (http://technet.microsoft.com/en-us/library/ms345167.aspx)

This was all assuming that you have many csv files that you would not like to build a specific data flow task to map your columns. If you just want to map a simple csv file to a database, this is a VERY simple process and you can follow some of the instruction on the tutorials (http://msdn2.microsoft.com/en-us/library/ms170419.aspx) or use the import / export wizard. Or, here is a sample of someone running through a simple flat file import through 2 lookups (it looks like they got the bulk of this form the tutorials... but at least they have plenty of screen shots) http://www.mridey.com/blog/Lists/Posts/Post.aspx?List=321eab0d%2De4d2%2D4523%2D914e%2D128a6688109f&ID=7

Eric, I appreciate the response. Sorry for my tone but I had two reasons for it. First, I really do want an MS employee to read it and possibly improve the situation. Second, I was coming off 27 straight hours of coding SSIS - with no success. Anyway, I finally did get it working. None of the samples helped until I stumbled upon something on a different site. The download link was broken, and the sample code had minor bugs, but all-in-all, it was really excellent. The process of going from a CSV file to a table (especially if you want to use non-char datatypes) is quite involved - far more code than I would have expected. I do have a question though. I noticed that a developer can use the object model, or he can use a little bit of the object model and an XML schema. There is a tool that takes an SSIS package as input and outputs a program that basically loads and executes an XML version of the package. So, if I want to dynamically change my package at runtime, I would simply need to alter the XML, reload, and execute. Which is the preferred method: declarative or imperative? If the declarative approach is preferred, where can I find the documentation on the XML Schema? Thank you for your help.

I am probably not going to be the best person to ask for a response on this one. I know how it is that one would build a package dynamically, but do not use this method myself as I have yet to really come into a situation that required it. If you wait around, I am sure someone else will be more than willing to help you out (however, today is a holiday for a lot of people and you will get less traffic as a result). What exactly are you wanting to change at runtime? If it is something simple, like a connection string to get to a different databse or csv you can do that through package configurations. If it is changing around the pipeline to handle data differently based on what you are importing it will probably require that you build this programmatically. If you are going to be going through and loading from these csv's each of which are different, but which will map one to one to a different database table, you will probably want to use the bulk insert method listed above...

I actually wound up using a DataConverter. It covered my needs and with a good bit of code it allows me to vary the number and order of fields in the CSV at runtime. All my CSVs load into the same database table, so I just need to deal with the "first row header/noheader" issue (which is easy) and the number/order of input fields. The destination fields always have the same name/type, though the destination table will change name each time (its a temp table for staging). All of that is working now, thank goodness. I used only the SSIS object model, but I did see that there is an XML based alternative. I was just wondering if it was documented or if it was ultimately going to replace the object model.

YADeveloper wrote: I do have a question though. I noticed that a developer can use the object model, or he can use a little bit of the object model and an XML schema. There is a tool that takes an SSIS package as input and outputs a program that basically loads and executes an XML version of the package. So, if I want to dynamically change my package at runtime, I would simply need to alter the XML, reload, and execute. Which is the preferred method: declarative or imperative? If the declarative approach is preferred, where can I find the documentation on the XML Schema? I'm curious about this tool you mention. Is it the Ivolva CodeGen tool (http://www.ivolva.com/ssis_code_generator.html)? My opinion on the declarative vs. imperative method: The XML Schema for SSIS files isn't published, and is subject to change. The API, on the other hand, is documented (somewhat ) and from my brief amount of time looking at the API in Katmai, should be a fairly easy transition. So I lean toward the API.

Yes, it was the Ivolva tool. Thank you for your thoughts on the XML. It seemed like a really elegant way to build the job. The XML is significantly smaller than the equivalent code. However, I did wind up using the API. One thing I could not find documented was the set of access mode codes. I wound up using the Ivolva tool to generate the XML from a packageI built in the designerand then I read the XML to find the enumeration constants. Do you know what page of the MSDN documentation gives the details on the access mode enumeration values (e.g. Table or View, Fast Load - Table or View, etc.)? Thanks...

I figured them out by reading the XML myself. As far as I know, there is no page documenting that in Books Online.

Eric, I appreciate the response. Sorry for my tone but I had two reasons for it. First, I really do want an MS employee to read it and possibly improve the situation. Second, I was coming off 27 straight hours of coding SSIS - with no success. Anyway, I finally did get it working. None of the samples helped until I stumbled upon something on a different site. The download link was broken, and the sample code had minor bugs, but all-in-all, it was really excellent. The process of going from a CSV file to a table (especially if you want to use non-char datatypes) is quite involved - far more code than I would have expected Dear YADeveloper, Would you mind sharing the location of this "something on a different site" you stumbled upon? and if the download link was broken, how did you get the sample code? Thank you for your help in advance.