.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

SSIS importing fixed length file to sql.

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

Im trying to import fixed length files to sql. data in it s not importing correct im getting the values incorrect.

it takes the rowdelimiter as also an character and calculating it wrongly. here s my code. if u can plz give some alternate sample code



void CreateSourceColumns()



// get the actuall connection manger instance


IDTSConnectionManagerFlatFile90 flatFileConnection =


View Complete Post

More Related Resource Links

Creating a Fixed-Length, Flat File, Space-Filled file


I need to create a Fixed-Length, Flat File, Space-Filled file. This is what I did:

1. Created a stored procedure with the fields using SPACE(#) so all the fields are the correct lenght.

2. Made a report.

3. Saved the report as Excel.

4. Save the Excel as 'Formatted Text Space Delimited'.

There are so many steps involved that I am wondering if there is a better way to create such a file.

Any help would be appreciated.




Load multiple record length text file into table using SSIS


I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Creating a Fixed-Length, Flat File, Space-Filled file


I need to create a Fixed-Length, Space-Filled flat file.

I have a SQL query that uses SPACE(#) to make the fields that correct size.

I tried using reporting services and exporting to excel and saving to .prn, but that does not work because the maximum width is 240 and I have 350 characters per line.

I am fresh out of ideas. Send help!!!

Thanks, Linda

Linda Fleming

Insert spaces to get fixed length fields for flat file export



I have a query which extracts all the required data from a database and I need to load this data into a Text File. I am using a SSIS package to do this.

Now this file should have data in the format given below

437856785        A20101009This is construction         124728347

i.e the receiving file has no delimiters and each field is determined with a fixed length(fixed number of characters). The data I get out of my query for example for the first column returns just the value 437856785 and I need to append spaces to it to achieve the fixed length. I need to do to this kind of appending spaces to all the columns. Please suggest me ways to do this.

Thanks in advance..



While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Convert Data from TXT file and face a length issue! Need help!

Hi All, I am trying to convert some TXT files in to sqlserver database by using SSIS.  The problem I am facing right now is that some TXT files I have are fixed with line length 286.  However, for the some other TXTs they have 296 characters per line. The reason why this happened is some one added one more field before generating those TXTs.  Instead of creating two SSIS packages for converting those TXTs, can I find another way to get around this?   

importing .csv file into MS SQL 2005

Greetings :-) I need to import data from my excel .csv file into a database in MS SQL server 2005. Can someone show me the steps? Is there a way to do this with a wizard since I'm not a DBA? Thanks in advance for your assistance   .

Problems running a bat file within a SSIS package

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

SSIS multiple data flows accessing the same raw file

I have multiple data flow tasks accessing the same raw file at virtually the same time.  I would like to know if the data flow tasks lock the raw file during read?  Can multiple data flow tasks read the same raw file at the same time.  I'm currently developing with a very small data set, but I would like to know if I should be concerned with the possibility of the data flow tasks competing for access to the same raw file. Thanks.

Problem importing text files with binary zeros (0x00) via SSIS(SQL2005). It is all fine when using D

Hi.   There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following: A17     123.17  first row          BB29    493.19  second             ZZ3     18947.1 third row is longer And in hex format: 00:  41 31 37 20 20 20 20 20 ? 31 32 33 2E 31 37 20 20  A17     123.17  10:  66 69 72 73 74 20 72 6F ? 77 0D 0A 42 42 32 39 20  first row??BB29 20:  20 00 20 34 39 33 2E 31 ? 39 20 20 73 65 63 6F 6E     493.19  secon30:  64 0D 0A 5A 5A 33 20 20 ? 20 20 20 31 38 39 34 37  d??ZZ3     1894740:  2E 31 20 74 68 69 72 64 ? 20 72 6F 77 00 69 73 20  .1 third row is 50:  6C 6F 6E 67 65 72       ?                          longer          I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).   The table is: CREATE TABLE eng ( src varchar (512) )   When i upload this file into SQL2000 using DTS or Import wizard, the table contains: select src, substring(src,9,8), len(src) from eng <               src                ><substr>             <len> A17     123.17  first row           123.17                  25BB29                                493.19                  22ZZ3     18947.1 third row           18947.1                 35   As one can see, everything was importe

SSIS 2005 - Foreach loop container - Stopping the loop after processing X number of file?

I need to stop the Foreach loop container from processing more files in the folder than desired. Scenario:  If I only want 1 files processed, i need to stop the loop after it finds 1 file.  No matter what the contraints or tests or variables I set, the loop processes all the files in the folder.  This is bad because I need the value of the 1st mapped variable and not the last one it finds. I have tried counting records and setting variables used in the contraints but to no avail  Nothing seems to stop the loop. Please advise

Error Importing SSIS Package

I can't import a package from the SSIS to the file system of my SQL Server Management Studio. Every time i've tried the following message appears. What should i do to correct this problem? Any ideas?   =================================== Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS) ------------------------------Program Location:    at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)   at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

SSIS throws error when it doesn't find file

Hello All, I am facing one issue that i dont know in advance if there is file on FTP or not. I scheduled a job which runs every hour which in turn calling SSIS package. Now, when there is file on FTP, it works fine. But, when there is no file on FTP then itthrows error which i want to handle. Is it possible to check for any files present on FTP? Cheers, Pranav

Saving SSIS results to Log or text file using dtexec

Hi, How to save the SSIS package results to log file using dtexec command............please help regaridng this...........   Thanks in advance,

SSIS FOR each loop to load 1 file after other into database for particaular folder

HI I am using ssis 2008,i have folder in which i have 10 excel file.i wont to load them one after other in data  base I have created a Dataflow task that load one file into database.i have inserted this dataflow task into For each loop and in For each loop i have give that folder path and *.XLSX  and in variable mapping i have created one variable  which i have passed in Database destination connecion string (properties->expression) but still i get error "cannot acquire connection from connection manager" please guide me or provide me with some reference so that i cud load file from folder into database one after other

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay

ssis 2008 Loading MULTIPLE EXCEL file

HI it is possible for me to load multiple text file using for each loop,but if i try the same process for EXCEL file then my excel source throws an error. Also when i created my variable in value  i have given the path of my excel file ,still it didnt help me..because the moment i map the variable with the connection string of source excel connnection(property->expression->connection string) it start giving error if you could help me or provide some reference it will be of great help http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html i tried this link but got stuck with it on step 12 and 14 (below  is my connection string) and variable is @Filename which i have created Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mushtaq.sheikh\Desktop\ForEach\New Microsoft Excel Worksheet1.xlsx;Extended Properties="Excel 12.0;HDR=YES"; The moment i try to edit my connection string by comparing connection string provided in link it gives error(an OLEBD error has occured with error code 0x80040E73) on excel source Thanks in advance
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