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

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

Import flat file from the report to filtered data

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server


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



View Complete Post

More Related Resource Links

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!  

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?

Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

How to import data from xml file into a dataset


I want to import data from a xml file into a dataset
this is my xml file  


<?xml version="1.0" encoding="UTF-8" standalone="no"?>


  <Person Personid="10">



    <Birthday>1360 </Birthday>














when I use this code
I have these tables in my dataset
But I want to have FIVE (more) tables for Fax,Mob

data type problem in import data from excel file


Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)

Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1


Data conversion failed --flat file connection manager


Hello All,

Can any one help me in this .

[Flat File Source [16]] Error: Data conversion failed. The data conversion for column "acct" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Flat File Source [16]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "acct" (373)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "acct" (373)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED.  Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.  There may be error messages posted before this with more information on why the thread was cancelled.


can any one help me in this error :


[Lookup [465]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Lookup" (465)" failed because error code 0xC020901E occurred, and the error row disposit

SQL Server 2008 R2 Disk Usage report showing incorrect data file


When I go to ANY database (System, AdventureWorks*, user) in Managemant Studio, I right-click on the db, select Reports, select Standard Reports, and select Disk Usage.  The disk usage report shows the correct db on top, but always only shows AdventureWorks_Data.mdf when I expand Disk Space Used by Data Files section.  This happens to any db I try.  When I look at Database Properties/Files for that database, the logical and physical file names are correct.

Since the correct files appear in Database Properties/Files, I'm hoping it's just a bug in the report.  Same thing happens if I run a Disk Usage by Table report for ANY database,  The tables for AdventureWorks always only appear,

Thanks,  Mike

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!!

Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?




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

How to Create multiple flat file import package?

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


Import data from excel file to datagridview in vb.net


 Dim DBConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\vb.net\2210201\new marks analysis system\new database\stu_basic.xls;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
        Dim SQLString As String = "SELECT TOP 1000 * FROM [Sheet1$]"
        Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
        Dim DBReader As IDataReader = DBCommand.ExecuteReader()
        dg.DataSource = DBReader




i am using this but its doing nothing .

no errors , no data from excel.

whats the problem in it.?

using openrowset to import data file



Just a quick question.  I am trying to import data from excel to sqlserver using openrowset.  Dumb question... when I specify a file location is it from server perspective or from client perspective?  What I mean is if I specify is the string c:\<directory name>\<file name>  will it be looking in directory on the server? 

If server can I use http notation to point to a client file?  file=http://input/test.xls



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

import filtered sharepoint 2010 list data into a word document

i want my filtered list data in sharepoint and export the data into a word document with some relevant fields not all the fields. how can i acheive this ?  
e.g i want to show all items related to "taskname" in word document.

How to import dbf file data into Sql Server r2 2008


Hi to all,

          I have a task to convert dbf file into  sql .Can any one tell me how to convert dbf file into sql server r2 2008.



Mohammad Siddiqali

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.
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