Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server


SuperbHosting.net have generously sponsored dedicated servers to ensure a reliable and scalable dedicated hosting solution for BigResource.com.





SSIS Import From Comma Delimeted Flat File


I have a data record as below from teh comma delimeted text file.

660,"CAMPO DE GOLF ""LA FINC ALOGORFA,",7941

SQL 2000 DTS loads this data fine whree the second column is loaded as

ABC ""DAT DESC,",



But Unable to load the record using SQL 2005 SSIS.It considers "CAMPO DE GOLF ""LA FINC ALOGORFA as one column and " as column 2. Is there any options to load this type of data using SSIS.

Thanks




View Complete Forum Thread with Replies
Related Forum Messages:
SSIS Flat File Import Help
I am trying to import a flat file into SQL Server 2005 using SSIS. I have never used it before and I am getting confused by the error I am receiving.

I have a link to a flat file, that gets sent through a Derived Column flow where dates in YYYYMMDD are changed to MM/DD/YYYY format. Then the string MM/DD/YYYY is converted to a date in a Data Conversion flow. And finally the data is put into a SQL Server table (currently with no rows).

The problem I am having is with a text field with the email address in it. The error I am getting is:

[Import Allstate Auto Club [1]] Error: Data conversion failed. The data conversion for column "email_source" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

The problem is I can't see where in the flow the problem is. The field length is 20 wherever I look and the codepage is 1252 wherever I look. Does anyone have an insight? Keep in mind, I have never used SSIS before and I consider myself an amateur with SQL Server. It could easily be a data type conflict or something easy. Any help will be appreciated.

View Replies !
SSIS - Import Flat File
I have a fixed width flat file I'm trying to insert into an SQL 2005 table using SSIS -- it's a recurring task. One of the columns in the flat file has to go to a column of type Numeric. No matter what I try : a data conversion, defining the field as DT_NUMERIC in the connection,... I always get "The conversion returned status value 2 and status text :The value could not be converted because of a potential loss of data". It is driving me bonkers, up to the point that I find myself wishing for the 'good old' DTS days of SQL 2000. And I dread to think what will happen when I try to port some serious, much more complex DTS packages on my SQL 2000 to SQL 2005.

The data in question represents longitudes and latitudes so quite often there is a leading white space in the data : ex. : " 95.15". Surely that cannot be the cause ?

I've spent hours doing the RTFM-thing and searching the newsgroups, fora...you name it. Apart from ending up running in circles in the MS documentation, the only thing I've really learned so far is that I'm aparently not the only one driven to dispair by the new SSIS thing.

I can think of a number of ways to hack my way around this thing, but that's not the kind of 'progress' I had in mind when I started the move to SQL 2005.

Intelligent suggestions would be most welcome.

View Replies !
Update Flat File Before Import SSIS?
 

Hi,

We have a csv file which contains a date field. The data in the field contains "0" as well as "dd/mm/yyyy".  Is it possible to update all "0" to "01/01/1900" on import using SSIS.

Basically when we import the flat file now it falls over due to the destination table data type being datetime.

If this is not clear please let me know and i'll try and explain more?

Thanks for any help.

Slash.

View Replies !
SSIS Import/Export Flat File
Firstly, I hope this question isn't asked too frequently but I found no existing reference to this situation....

I had a bunch of stored procedures in SQL 2k which imported and exported data to and from flat files using TEXTPTR, READTEXT, UPDATETEXT etc... The flat files were continuously changing so the filepath was a parameter for the sp.

The reason I used the pointer to flat files is because I didn't want to
load the files in memory before commiting them ie. with TEXTPTR and
UPDATETEXT I can import a 1Gb binary file 80000 bytes at a time and
keep (precious) memory usage down.


I was accessing this procs from a C# application.

Since these methods are going to be phased out by the guys at MS what is the best way of importing/exporting very large binary files in SQL 2005?

As far as I can tell SSIS requires a Flat File Source Manager object which needs a static filepath - not good.

Hope you can help,
Paul

View Replies !
Error 0xC0047012 Trying To Import Flat File In SSIS
 

I'm trying to do a simple flat file import of a .csv file.  The task keeps failing on me and I get the following error

 Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039

I looked up the error codes and the only information I can find is that a thread is failing.  What would cause this and how can I fix it?  I can open the same file in Excel without any problems.  I'd really appreciate any insight that anyone has to offer.

View Replies !
SSIS - Delete Rows Before Flat File Import
I finally put together a SSIS package that takes a Text File and successfully imports its data into the right table. My question is, where in the package's properties can I find the option to Delete all rows from Destination Columns prior to Importing. I have looked everywhere in the Package Explorer for this setting. Thanx in advance.

View Replies !
How Do I Import A Varying Column Width Flat File Into A Table Using SSIS?
How do i import a Varying Column Width Flat file into a Table using SSIS? 


I have a flat file that has 4 columns with varying width
Like I should read the file as
Col 1 - (1  to 10 Characters)
Col 2 - (12  to 21 Characters)
Col 3 - (22 to 35 Characters)
Col 4 - (36     to 38 Characters)
At the end of the record is a "LF"
 

I think "Fixed Width" Columns allow me to define a standard column length for all the columns.. Right?

Any thoughts on how to?

View Replies !
Flat File Comma Delimited Ignoring Quotes
 

hi im using the import export wizard to import a file where my recoreds are comma delimited but when i HAVE to use a comma im using quotes to escape

"CN=Administrator,CN=Users,DC=aboneng,DC=local",user,Administrator,Built-in account for administering the computer/domain,"CN=Administrator,CN=Users,DC=aboneng,DC=local",4,20050421014154.0Z,20060518065932.0Z,
 
with this example

CN=Administrator,CN=Users,DC=aboneng,DC=local      === should be the first column
user,   ==== the second column
Administrator === third and so forth.
 
i thought it was a standard to use quotes when escaping your delimiter but sql is bringing in the records as comma delimited as normal is there anyway around this?

View Replies !
C# Application - Method To Import Delimeted CSV To Sql Server Table
Hi,I'm writing a telecom billing system where it is necessary to import csv delimited files into a sql server table.
1) The csv files to import differ in column arrangement i.e SQL server table arrangement = A, B, C, D whilst CDR = - , A, -, C, D2) The csv files are delimited via different characters i.e some are del.. by commas some by semi colons
 I can import a CSV file from my application using a BULK INSERT query but only using CSV that resembles the structure of the table exactly
How can I pass a CSV to the table and state where I want each CSV column to fill a column in the SQL SERVER table
Below is how I am managing to pass a CDR to the SQL table that I have pre manufactured to resemble the structure of the SQL table Is there a way I can state which values from the CSV I want to pass to corresponding columns in the SQL table:
 
string conString = @"Provider=SQLOLEDB;Server=(local);Database=Billing;Uid=sa;Pwd=sa;";
for (int i = listBox1.Items.Count - 1; i >= 0; i--)
{
string strSQL;
OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
objConnection = new OleDbConnection(conString);
 
string cdr = listBox1.GetItemText(listBox1.Items[i]);
 
strSQL = "BULK INSERT Campaign FROM '"+ cdr +"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '')";
OleDbDataReader objDataReader;
 
try
{
objConnection.Open();
objCmd = new OleDbCommand(strSQL, objConnection);
objCmd.ExecuteNonQuery();
objCmd = null;
objDataReader = null;
objConnection.Close();
listBox1.Items.Remove(listBox1.Items[i]);
}
catch (Exception ex)
{
objConnection.Close();
}
objCmd = null;
objConnection = null;
}
 
Regards
 Simon

View Replies !
Import Into Comma Delimited Text File (very Urgent)
I want to join differnet tables and import the data into comma delimited text file. There will be lot of checks like if then else to manipulate data. I want to use stored procedure but don't know how to output to text file. Is there any utility which can be used in stored procedure. In future this will be run as an automated job.
Thanks in advance.

View Replies !
Flat File Import
I am trying to import a flat file with large rows into MS SQL Server. This flat file consists of about 100 columns of data, followed by a set of 10 columns repeated 50 times.

I would like very much to break the data apart in the import. What's the best way to handle it?

--
Dyolf Knip

View Replies !
Dts Import Flat File
Hello, I'm trying to import data from a flat file into a table that has smalldatetime data types. I tried creating triggers on the smalldatetime data types that converts the data from a string to a datetime value but the import is still unsuccessful. What should I do?


Col002 looks like this in my flat file 'ex: 20000112'
DTSDestination("entry_dt") = DTSSource("Col002")
I get an error when trying to put the value of col002 into entry_dt.


Thanks.

View Replies !
Import Flat File
I am trying to import a flat file using SQL Server Management Studio and am receiveing the error:
Error 0xc0202055: Data Flow Task: The column delimiter for column "Column 19" was not found.
 (SQL Server Import and Export Wizard)

I would like to capture the rows that are causing the error and have the import continue.  Am I able to edit the behavior somehow?Thanks.

View Replies !
Import From Flat File
 

Hi,
 From the filemaker database, I am exporting data to the Flat file separated by tabs. From this Flat file I need to import into Sqlserver table. The Flat file has the 23 columns and 4000 rows.
To insert into Sqlserver table I am creating the Flatfile connection.
In the dataflow tasks, I am creating the Flatfile source, OLEDB destination and mapping the columns. And to run the package I am doing the Debug->start with out debugging.
In my flat file I have columns like SSN, Email. So in Sqlserver table I am defining the columns as the Nvarchar(200).
If I insert the 6 rows data from flat file to Sqlserver, this works fine.
 
If I tried to insert the 23 rows data, by clicking on Debug->Start debugging -> no data inserted into Sqlserver table. the arrow between flatfile source, OLEDB destination is showing the 450 rows. How can I view the errors?
 
When I mouse over on the OLEDB destination, it is showing "truncation occurs on the column (20th column) more than 512 characters.."
Initially the Output parameter length is 50 and datatype is Unicode char, I increased it to 512.
What is the problem here? If there are more columns I am not able to insert data.
How to view the errors?
 
Thanks

View Replies !
How Do I Import A Flat File (.txt) To Sql Server Using Asp.net
 Hi.

  I want to import a flat file to my sql server database. My sql server and web server are on different machines. I used a bulk insert to import the data using a Format.. But now since the sql server and the web server are on different machine it doesnt load the data to the sql server..

i have tried giving http://Ipaddress/Path and that didnt work.. tried mapping the network drive to the webserver and then specified the location and that didnt work too..

I found this connection string on the internet
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c: xtFilesFolder;Extended Properties="text;HDR=Yes;FMT=Fixed";

but i am not sure how to give a ~ delimited and specify a format file..

any help will be appreciated..

Regards,
Karen

View Replies !
Advice On Flat File Import
I€™m looking for advice for the following scenario:

Import Source:  Flat File €“ 2 columns (student#, lunch_bal)

Destination:  SQL Table:

Trnpayuniq (PK)

Atype  (all values = €˜S€™)

Auniq ( from flat file €“ student#)

Trnamt (from flat file €“ lunch_bal)

Trnpayc  (all values = 1)

 

I€™ll truncate the destination table before each import. 

Is the best way to create a temp table; get it populated
then update the destination table?  Or
would the use of Merger (Merge Join) be the better approach.

Thanks for any direction.

View Replies !
Question On Flat File Import
I have a flat file that uses tabs as the column delimiters and cr-lf as row delimiters.  The first portion of the file consists of only two columns for approximately 10 rows and then the file changes to 4 columns for the balance of the file, about 21 rows.  The column names are in the first column and the data of interest is in the second column for the first 10 rows and then in the third column for the last 21 rows.  Is it possible to set up something like this for parsing in SSIS?  I've tried using two columns in the data flow task but then I get columns 1 and 2 through the whole file.  If I tell it there are 4 columns in the file, it appends rows to each other so that there is a total of 4 columns in the first 10 rows.  This reduces the row count to less than 10 and the data in these rows isn't in the proper place.  Is there a way to handle this file in SSIS?

TIA

View Replies !
Flat File Import Question
I have a fixed width flat file that I'm trying to import, and I'm just about there.  The last column that I'm struggling with, is a decimal amount.  The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000  In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4)  I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000  Is there any way to achieve this other than using a script component to calculate the value?  Thanks!

View Replies !
Simple Flat File Import
Boy, do I need HELP!  Have a simple csv file that I need to import.  Worked fine in sql2000; I put it into dts to execute on a monthly basis.  Makes connection, db connection, table creation fine, but stops at validation of flat file?

Basically, I want to go out and get a flat file, drop the existing table, and create the table, and import the information from the flat file.  Not a complicated table of about 30,000 records.

Create table [db].[dbo].[tblPatient] (
[patientID] into not null, [chartID] varChar(15) null, [doctorID] int null, [birthdate] datetime null, [sex] varchar(1) null, [raceID] int null, [city] varchar(100) null, [state] varchar(2) null, [zip9] varchar(9) null, [patientTypeID] int null, [patName] varchar(100) null)

  Below is the error report that tells me NOTHING!

Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Error)
 Messages
 * Error 0xc00470fe: Data Flow Task:
 The product level is insufficient for component "Source - pmPatientInfo_csv" (1).
  (SQL Server Import and Export Wizard)
* Error 0xc00470fe: Data Flow Task:
 The product level is insufficient for component "Data Conversion 1" (71).
  (SQL Server Import and Export Wizard)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Success)
- Copying to [fhc].[dbo].[tblpatient3] (Stopped)
- Post-execute (Stopped)
- Cleanup (Stopped)

View Replies !
Import Flat File Automatically?
Hi,

I'm going to be getting several flat files that need to be imported into one of two tables.  Although the text files will have different file names, they will have either "Header" or "Detail" in the file name, so I can tell which table they need to be imported into.

The problem is I don't know enough about SQL Server 2005 to set up an automated import of these files into the database.  Does anyone have any suggestions on how to do this, or have any experience in setting this up?  Are there any inexpensive programs to load data realtime or on a schedule?  (I work for a young company with a very tight budget).

I had an idea of creating a windows vb or batch program to create the import commands for each of the flat files, but I can't find the line command to build the import command.

I'm just at a loss and need a solution soon...

Thanks,

Laura

View Replies !
Import Flat File Data
Hi,





I wanted to know if there was a way to import data from a flat file without specifiying the delimiters. I want to import each line in one row so that i can use the substring function to break of the data as an when i want and not as per the delimited format file or the wizard.

i.e if row one had  "abc"|"1453"|"Jack"|"Smith"| etc.... rather than importing these as different columns and rows. I want this all in one row, one column.


Is it Possible?

View Replies !
Import Flat File To A Sql Server Database..
Hi.
   I want to upload a Flat file from an asp.net website to a Sql server.. I have used olebb provider to upload excel files and a foxpro provider to upload DBF files and then used the sqlbulkcopy to put the data to my sql server..
 
SO i was wondering what kinda provider should i use to import a Flat file to the database and do i have use a format file in order to import it...
 Any help will be appreciated..
Regards,
Karen

View Replies !
N00b: Best Way To Import Flat File With BIDS
Hi all,


I'm totally new to SQL Server 2k5 and need to do something rather basic: import some CSV files into tables. I'm getting translation errors and would like to know what's the best way to cast the strings before inserts.

I'm doing the import in BI Development Studio.

Current situation:
Created connection managers to csv files
created SQL server destinations pointing to the tables
connected them directly with a dataflow path
Ran the packadge: one import went just fine, the other one complains about conversion errors like "Conversion DT_STR and DT_I4 not supported"


Both tables have the same kind of fields (varchar, float, datetime, int)

I looked at converting the data using a transformation but am somewhat confused of which one to use.


What's the best way to transform the data before insert: derived column, import column or data conversion? Or something else I overlooked?

TIA

Peter

View Replies !
Flat File Records Dropped During Import
Hello,
I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

Thank you for your help!

cdun2

View Replies !
Import Flat File To Sql Server Programmatically
Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

 

thanks & regards,

sivani

 

 

View Replies !
Import From Flat File Warnings And Errors
 

Hi,
 I am trying to import from Excel file. So In between Excel file source and OLEDB destination I am using One Data Transformations to convert excel unicode characters to Sqlserver varchar.
 
Iam getting this following errors:
1)

Error: 0xC020901C at Data Flow Task, OLE DB Destination [382]: There was an error with input column "Copy of Zip" (615) on input "OLE DB Destination Input" (395). The column status returned was: "The value could not be converted because of a potential loss of data.".

 
Copy of zip is the Data Transformation column mapped Sqlserver Varcahr(200) column of Zipcode.
In excel file the Zip codes are like this:
78712-2344
78123
12345
87651-1234
 
2)

The column "State" needs to be updated in the external metadata column collection.
This is warning. This type of warnings are for all columns in excel file.
 
3) Intially I declared the Sqlserver table columns like this Varchar(100), then SSIS showing some warning like truncation of column State 255 characters to .. So I changed columns datatype from Varchar(100) to Varchar(500)? Why we need to change like this.
 
Thanks in advance

View Replies !
Import From Flat File Having 2 Types Of Records
 

Hi,
I have a flat file that contains 2 types of records - Dev and production.  The Dev will be noted with an D and the Production with a P.  These records are different - The dev records are in a different order and contain different info then the Production.  I need to use SSIS to import the data into 2 different SQL Tables. How to do this?
Can any one help me
Thanks in advance

View Replies !
Export To Flat File Using T-SQL And Import To Another Machine
 

Hi,
 
I need to export some data from SQL 2005 to a flat file, The data and flat file names will be dynamic and will be be fired programaticaly so I can't use DTS or SSIS.
 
In SQL2000 I did it using bcp, but that's quite a security hole so I don't want to use external utilities. I'll need to do something similar on another machine to import the data as well.
 
I find it strange there's no easy way to do this!
 
Thanks.

View Replies !
Date Being Discarded In Flat File Import
I am importing from a flat file source that is pipe delimited.  I have a few files that all follow the same format but now that I am dealing with subsequent files, they are reacting differently and the date is being discarded.  I have opened the subsequent file into Excel without any column conversion issues.  But whenever I run the package, about halfway through the dates become NULL.  I have defined the field as DATE, DatabaseTimestamp, String (convert to Date using substring parsing method) and all of these yield the same results.  Any ideas?

View Replies !
Flat File Import Performance Question
Hi there,

I have a question regarding 2 approaches importing data from a flat text file. I'm taking the approach suggested by Phil Brammer to use a checksum to see whether a row has exists, and if it does check whether it has changed.

http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed

I would like to hear some opinions on 2 approaches:

1) Importing the data int a temp table first then do the data processing from the temp table.
Or
2) Importing the data by doing the data processing direcly from the text file without placing it in a temp table first.


A hunch told me that it will be faster doing approach 1 but I would like to hear some experienced opinions first

Best regards
Mike

View Replies !
How To Import From Flat File And Update DateTime C
I have a a flat file that consists of 2 Columns of data that need to overwrite an existing Table that has 3 Columns of data. The Import fails because the 3rd column on the table is a Date stamp column with the Data Type of "smalldatetime" and does not allow Null data. If I were to delete this 3rd column from the table the import works great but I lose the DateTime column. How can I use the Import Wizard to import the first 2 columns from a text file and update the 3rd column with the date and time? The wizard does not seem to let me update a column unless the data for this column comes from the flat file. Please assist, thanx.

View Replies !
Struggling To Import Data From Flat File To Sql Db
Hello all,

We have been trying now for the past 2 days to import data from a flat file to sql server database but with no luck.

The real issue here is that one of the field names has a very long value.

As a result, the import fails because it is unable to truncate the value.

We really don't want the value truncated but we have not been able to import the entire data file.

We have used nvarchar(max) but it doesn't work.

Can someone please let me know if you have encountered this type of issue and how was it resolved?

Thanks in advance.

View Replies !
Import Flat File Into SQL Server 2005 Express
I am new to SQL Server, and migrating part of an Access application toSSE. I am trying to insert a comma delimited file into SSE 2005. I amable to run a BULK INSERT statement on a simple file, specifying thefield (,) and row () terminators. I can also do the same with aformat file.Here is the problem. My csv file has 185 columns, with a mixture ofdatatypes. Sometimes, a text field will contain the field delimiter aspart of the string. In this case (and only in this case) there will bedouble quotes around the string to indicate that the comma is part ofthe field, and not a delimiter.Is there any way to indicate that there is a text delimiter that isonly present some of the time?If not, any suggestions on getting the data into SSE?Many thanks for your input.Cheryl

View Replies !
Multiple Flat File Import To Corresponding SQL Server Tables
 

We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.

Thank you very much,

Regards,

Srinivas Raveendra

View Replies !
Import From Flat File Source - Datatype Conversions
We do a lot of importing from multiple Flat File Source data.  Is there a place where I can
change the default datatype conversion for Flat Files from nvarchar to varchar? I found the
"%PROGRAMFILES%Microsoft SQL Server90DTSMappingFiles" location but I'm not sure
which file is for "Flat File Source"


Thank you,
-Rick

View Replies !
Sample On How To Import Flat File With Vertical Structure
Hello,

I'm looking for an example on how to import a Flat file that is not a csv file but had data on several lines, a small indicator at the beginning of the line indicates what kind of data is on the rest of the line. Not every field is availabye for each block.

Anybody got an idea on where to find this kind of samples.

Thx.

View Replies !
Flat File Import, Ignore Missing Columns?
 

I'm new to SSIS, and trying to automate data imports from text files.  The text files I'm importing always contain a fixed set of columns, or a subset of those columns.  If I include a subset of columns in the import file (and exclude others), the data doesn't import...I assume because the actual file doesn't include every column defined in the flat file source object?
 
Is it possible to accomplish this without dynamically selecting the columns, as indicated here: http://msdn2.microsoft.com/en-us/library/ms136020.aspx

View Replies !
How To Import From Flat File And Update DateTime Column?
 I have a a flat file that consists of 2 Columns of data that need to overwrite an existing Table that has 3 Columns of data. The Import fails because the 3rd column on the table is a Date stamp column with the Data Type of "smalldatetime" and does not allow Null data. If I were to delete this 3rd column from the table the import works great but I lose the DateTime column. How can I use the Import Wizard to import the first 2 columns from a text file and update the 3rd column with the date and time? The wizard does not seem to let me update a column unless the data for this column comes from the flat file. Please assist, thanx.

View Replies !
Read Text File From Flat File Connection Manager SSIS
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
 
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{
 
//Some code to read file and write it into new file

return DTSExecResult.Success;

}
 

public const string Property_Task = "CustomErrorControl";

public const string Property_SourceConnection = "SourceConnection";

 

public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)

{

if (node.Name != Property_Task)

{

throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));

}

else

{

try

{



_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;



}

catch (Exception ex)

{

infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);

}

}

}

public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)

{

try

{

// // Create Task Element

XmlElement taskElement = doc.CreateElement("", Property_Task, "");

doc.AppendChild(taskElement);

// // Save source FileConnection

XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);

sourcefileAttribute.Value = _sourceConnectionId;

taskElement.Attributes.Append(sourcefileAttribute);

}

catch (Exception ex)

{

infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);

}

}
 
In UI Class there is OK Click event.

private void btnOK_Click(object sender, EventArgs e)

{

try

{

 

_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);

btnOK.DialogResult = DialogResult.OK;

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

#endregion

}

View Replies !
SSIS - Data Flow To Flat File - Insert At Start Of File
Hi all,
 
In a foreachloop, I am inserting records into a flat file which is working fine. But the thing is that as the file grows, it takes longer for it to locate the EOF(End of File) of the flat file so as to insert the records.
 
I have around 70-100 lines written to the file at each loop and there are more than 20k records to be looped. wihich means that at the end I should be having 1400k - 20000k line in the text file.
 

One solution would be to insert the records at the start of the file itself so that it does not has to lookup the EOF each time before writting.
 
Another would be to generate separate files and then merge it. 
 
Any idea how can this can be done?
 
 
Beside this I have to zip the file and then SFTP to a given address.
 
Any suggestion or help would be welcome.
 
 
Rdgs
 
David
 
 
 
 

View Replies !
Flat File, Fixed Width Import With Nulls Always Fails
More SSIS woes. DTS was so much easier.

I have a flat file. It's fixed-with with CRLF record delimiters (a.k.a. Ragged Right format).

Some fields are null, and represented by the text NULL.

I'm trying to import the file into SQL via an OLE DB connection. The target table is a SQL 2000 data table. Two of the fields in the target database are of type smallint.

When I run PREVIEW on the data source (Flat File), everything looks good & correct. I added the convert columns task to convert my strings to smallint. This is where things go haywire.

After linking everything up, the conversion gives me a "Cannot convert because of a possible loss of data." All of my numbers are < 50, so I know this isn't the case. Another SSIS bogus error

My first instinct is the SSIS doesn't understand that NULL means null. I edited the file and replaced all instances of NULL with 4 emtpy string chars. Still no good. It seems to be having a hard time parsing the file now.

I dropped the convert task and tried editing the data source, and set the two smallint fields to smallint instead of string (SSIS formats). I get the same conversion error.

Changing the NULL values to 0 fixed the problem, but they're not 0. They're null.

Short of creating another script that converts all zeros to NULL using the aforementioned hack, I'm out of ideas.

 

I'm I missing something or is SSIS just incapable of handling nulls in fixed-width flat file formats?

TIA

View Replies !
Header Rows To Skip On Flat File Import/Export
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard.  Column names are specified within the first line of the file.  The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".

Why isn't zero (0) a valid numeric value?   I don't want to skip any rows.   PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist.   I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.

What am I missing here?  I installed SP1 of SQL server 2005, but that did not help.

Thanks in advance.

 

 

View Replies !
Default To NULL Instead Of Blank/empty String During Flat File Import
Hi,

In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.

The table may contain column that are NULLable BUT there is NO DEFAULT for them.

If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?

I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:

1- set the DEFAULT(NULL) for EVERY column that needs this behaviour

2-set up some Derived Column option in the package to return NULL if the value is missing.

Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.

 

Thanks

Anatole

View Replies !
Error With Text Qualifier In Qualified Field During Flat File Import
 

We have a flat file import proces which imports data from a series of unicode flat files.
 
The files have text qualifiers and are being imported to a table with the following format:
CREATE TABLE [dsa].[OBS](
 [Kundenummer] [nvarchar](10) NULL,
 [Navn] [nvarchar](60) NULL,
 [Adresse] [nvarchar](50) NULL,
 [PostnrBynavn] [nvarchar](50) NULL,
 [Kursusdato] [datetime] NULL,
 [Varighed] [decimal](18, 2) NULL,
 [Kursustype] [nvarchar](100) NULL,
 [Risikokoder] [nvarchar](50) NULL
) ON [PRIMARY]
 
In one of our files we have two rows that looks like this:
"19298529";"THIS IS ROW 1";"ADDRESS 9 -13";"4200 SLAGELSE";"02-05-2006";8.00;"Kombikursus Førstehjælp - Brand 8 lek.";"37"
"19448242";"THIS IS ROW 2";"ADDRESS 50";"4140 BORUP";"04-05-2006";4.00;""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r";"22"

 
Both rows are OK according to the format, but the second row actually contains the text qualifier in one of the qualified fields (""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r"). It's the title of a course with a comment.
The proces fails on this file, and wont even redirect the row, as it does on other erroneous rows in other files we import.
 
We believe this is a valid text, but apparently SSIS doesn't
Is this a bug or is this record not allowed?
Is there a work around, and why wont SSIS redirect the row?
 
We believe the reason is that the field before is not text quaified (which is of course specified in the connection manager).
 
Thanks in advance,
 
Lasse

View Replies !

Copyright © 2005-08 www.BigResource.com, All rights reserved