How do I use SSIS programatically? I got to know that there are few dlls I need to reference using which I can write code. like - Microsoft.SQLServer.DTSPipelineWrap Microsoft.SQLServer.DTSRuntimeWrap Microsoft.SQLServer.ManagedDTS Microsoft.SQLServer.PipelineHost Where do I get these dlls from? Does it come as part of Visual Studio 2010 any edition? or does it come as part of SQL Server 2008 any edition? Or is it that I need to install Business Intelligence Development Studio and use it to develop the SSIS programming there. How do I handle deployment? In my situation, I need to import flat files to SQL and the formats may vary. So I may need to create many packages. How do I handle it? Can I save the packages to my own database? PLease suggest...

you can do it without programming, you can loop through flat files, and use BULK INSERT command to import them into sql server with execute sql task, in this way you shouldn't be worry about different data structures. This is a sample of BULK INSERT which insert csv file into sql server: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/ tell me where you need more details?http://www.rad.pasfu.com

Thanks for a quick response Reza. I have looked into BULK INSERT and it is good. Then what is the difference between these 2. The BULK INSERT and SSIS? WHen do we use SSIS then? I have scenarios where I will need to map fields from source to fields in destination. I know that there is a Format file that we can specify in the BULK INSERT and this is in the form of XML file. We will need to store this file in a directory. I do not want to get into that. I was feeling that while using SSIS, we will end up creating packages and was of an understanding that we can save it in DB. Can you please help me understand this correctly. Regards, Santhosh B

you can use the BULK INSERT Command in execute sql task inside a foreach loop container which loops through flat files. actually you create BULK INSERT Command dynamically and then just run them with execute sql task. This is whole scenario, But if you need more details, this is an overview of details: add a foreach loop, set enumerator as file enumerator, set source directory, then in variable mappings tab of foreach loop, set a string type package variable with index 0, this will store filename on each iteration in the variable value. add another package variable of type string, write the BULK INSERT Command in the expression value of package with help of previous variable , set the EvaluateAsExpression property of this variable to true. then add an execute sql task inside the foreach loop and set second variable there. tell me where you need more details?http://www.rad.pasfu.com

Ok... let me explain my scenario... I think I have explained it wrongly...:) I have many flat files and they each can come with different formats.ie, the columns or sequence may be different or may be the values may need some manipulation and then the data needs to be moved the SQL server. So inorder to do that I will have a UI where the user can do the Mapping, the data manipulation required, show some sample data from the flat file and then import to SQL. To do the above mentioned requirement, I thought it is painful to use BULK INSERT because, - I will need to handle 2 types of files (format file & error files). - If I want to manipulate the data for ex: Pretext or POstText the data or may be replace the data with another, I dont know if I can do it here. If I can do the above requirements using BULK insert please let me know. Else, can you help me out understanding the SSIS question I had asked earlier? Thanks, sbalak

OK, if you need data manipulation so you can not use BULK INSERT, this is a sample of creating package and building it programmatically : http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64572/http://www.rad.pasfu.com