.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to Create multiple flat file import package?

Posted By:      Posted Date: October 26, 2010    Points: 0   Category :Sql Server
Hello Everyone,

I am an absolutely beginner to SSIS and need to perform the following task.

  • Need to import Master data from (semi colon) ; delaminated flat files into separate tables in SQL Server
  • Each file will create a separate table in SQL Server
  • While importing I need to perform validation on each file and only extract data that is valid e.g. CNIC column must be numeric and 13 digits long.
  • Any data that is not valid will be sent to a separate table with same TableName_bad suffix
  • After the data is extracted I will extract the details data of only those records that have been extracted in master table before

I know about basic data flow controls etc but I don’t know which control will be better for which task. Please tell me what will be the procedure to fulfill my requirements. I will be extremely thankful.



Syed Afraz Ali


View Complete Post

More Related Resource Links

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

Do not create a flat file if no rows are read

Hi, I have a number of flows in a package that are reading to check for changes in data and then outputing those changes to flat files.  If there are no changes, that is, I read no rows in my oledb source, an empty flat file is created.  Consequently, the file is sent and the destination is empty for the next run time. This is not really the worst thing in the world, but I would rather not FTP off empty flat files, if only for the sake of efficiency.  Is there a way to prevent the creation of the flat file if no rows are going to be written? Mark

Import flat file from the report to filtered data



Is there a way to use a flat file which could be imported from the report in order to filtered the data displayed ?



Single Row (Flat File) to Multiple DataSets


I have a flat file with 320k rows.  Each row contains generic data such as a supplier number, plant name, and dock code.  Each row also contains 5 sets of 4 fields route name, region code, arrival date and departure date.  Each of the 4 fields begin SX_ where the X is the set number.  So for example the column names per row are:


What I need to do, using the flat file as a source, is to read the generic fields into one dataset and the SX fields into another.  I then need to copy these datasets to an OLE DB destination into their respective tables.  This is very easy using a Windows Service and VB.NET.  Anyone have any ideas how to accomplish this using SSIS 2008?

Create Excel file dynamically in SSIS package and fill it by resultset data returned by Stored proce


Hello Every One,

I have a scenario in which i am executing a Stored Procedure which returned a result set.

I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].

Point is that the excel file should have unique name so i want to give name as name_date_time.

So every time when package run new file is created with unique name.


Can any one suggest me who to create Excel file and fill result set data into Excel file.

And then finally send that Excel file ?







Package configuration file with multiple connection strings


I am using the configuration file to read the connection string in the SSIS packages. In one of my packages i have to use two different connection strings. If i specify the second connection string in the config file, then the current package works well but other packages fails during validation saying the second connection manager does not exist in the package. Is it not possible to have multiple connection string in a single config file?

SSIS: Error in the package when the data in the flat file source is modified


Hi All,

I have a package which loads data from a flat file source to an OLEDB destination, which is executed successfully and data is loaded perfectly.

But later when the data in the flat file source is modified i.e. if an extra column gets added to the text file, the package is throwing an error because it is unable to detect the extra added column.

How can i avoid this error??? I need my package to execute successfully ignoring the changes(added columns) happened in the flat file source.

Please provide me wth your suggestions and solutions....

Thanks in Advance!!




I want to import values of current_time, name, and age from the XML file below. Then I will export those values into sql.

by using "xml source" I can recieve values from name and age, but not from current_time.

my question is, how to get

Import List of Worksheet Names from Multiple Sheets Excel File

I am only interested in OPENROWSET/OPENQUERY or xp_cmdshell solutions. Thanks.
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

SQL 2005 Import of Flat File always truncates data longer than 50 chars

My source data is a flat file, semicolon-delimited, and has three columns.  The first is 10 chars, the second can be up to 240, and the third can be up to 240.  For the import I select "Flat File Source" for the Data Source.  When I select my source file, I use the Flat File Manager, Advanced tab, to configure it as follows:

I leave the defaults for Column 0.
For Column 1 I select a DataType of Unicode String and set the OutputColumnWidth to 240.
For Column 2 I select a DataType of Unicode String and set the OutputColumnWidth to 240.

On the Destination page I choose "SQL Native Client", and point it at my database.  The destination table has 3 columns.  Column 0 is CHAR(1), Column 1 is NVARCHAR(240), and Column 2 is NVARCHAR(240).

However, when I get to the "Select Source Tables and Views" page of the wizard and hit the "Edit" key under "Mapping", it shows a size for all three columns of "50".  Sure enough, when I perform the import, it truncates everything to 50 characters.

How do I get more than 50 characters to import?

Embedded quotes in CSV file - Flat file import

I have embedded double quotes in one of columns of my CSV file. E.g (see 2nd line) "Sateesh","Maduri",1,M "S""Folk", "M",1,M And as we dont have direct configuration properties for this type of flat files while importing into sql server, I have used the example given in http://www.ideaexcursion.com/2008/11/12/handling-embedded-text-qualifiers/ , but I am getting the error "value of type string cannot be converted to Microsoft.SqlServer.Dts.Pipeline.BlobColumn'. I have one column as Text column which have embedded double quotes in it. Please help me as soon as possible.
Sateesh Maduri

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

Upload Multiple File using JQuery in asp.net

In this article I have explained how to upload multiple files using file upload control. I have used jQuery plugin for uploading multiple files.

I have also explained how to check for file type, file maximum size limit using jQuery & also using server side validation code.

Import XML File to SQL Table

Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Toolbox: Easy File Backup, Exploring Files And Folders Inside Visual Studio, Multiple Monitor Softwa


If the responsibility for creating, managing, and executing routine backups is yours, these tools will make it easier. Also see how you can browse folders and files from inside Visual Studio.

Scott Mitchell

MSDN Magazine May 2009

file upload in Create User Wizard Created User event with UpdatePanel Tag wont work?



In my Create User Wizard1 Created User Wizard Event, I am collecting extra information from text boxes and allowing

the user to uplad a file.



I have put the Submit button within an Update Panel Tag but since I have done this it does not work.

Is there a way round it. In this situation would you use a trigger.?


<br />

  <asp:UpdatePanel ID="UpdatePanel2" runat="server">

  <asp:Button ID="StepNextButton" runat="server"  CommandName="MoveNext"  align="middle"
                                            Text="Submit my Profile" />

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend