Solved

SQL Server SSIS - Flat File Import to Staging Table

Posted on 2010-08-24
5
1,462 Views
Last Modified: 2013-11-10
Hello,

I am importing a flat file source (csv) to a staging table, the flat file contains about 50 rows, and about 4gb of data.

Now, when I first define my flat file source, in the connection manager every row is defined a DataType of string [DT_STR] with a length of 50.

Is it suggested to use the "Suggest Type" button to have SQL define my rows or manually define them? Then, apply the same design to my staging table rows?

What is happening if I do use suggest type, then going straight to my database, I left SQL create the db, I get the following error:

Error: 0xC02020A1 at Stage Sales Extract, Flat File Source [41973]: Data conversion failed. The data conversion for column "COMMODITY" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

The COMMODITY row in the CSV file is defined as eight-byte signed integer [DT_I8], and in the staging table it is defined as "bigint".

When I say I let SQL create the table, in the OLE DB Destination, when I click it I have the option to select a "New" table, and the code is predefined for me with the data types and lengths.

Should I be manually defining my Data Types from the CSV to the staging?

If so, anyone have an example of the data types that I should be assigning to my rows based on the data in there and the finally in the staging table design?

New to SSIS/SQL, this was a project thrown at me and I am trying to get through this.

If I use [DT_STR] when defining my CSV Data Type (this is numbers and characters?), this should be set as varchar(lenght) in the table design?

Thank you for your help.
0
Comment
Question by:mirde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33512840
I would suggest you to look at this Link, it maps SSIS data types to SQL Data types
http://msdn.microsoft.com/en-us/library/ms141036.aspx

If you are unaware of the Data or Length of Data in the File. I would suggest you to use (DT_STR) with length (as suggested) .. It is advisable to build your own table accordingly and then Select from the Table List in the OLE DB Destionation..

(DT_STR,12,1252) -- means that it is a string dataType, with length -12 and Codepage -1252 (Default) that should be transformed to Varchar(12) in SQL


0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33513526
I generally take the approach that I have no expectations of my incoming data, so I import the data into a staging table that is completely defined as VarChar(xx).  Once it is in the saging table, I then do the manipulations, conversions, etc., in order to move it to the target table.
0
 

Author Comment

by:mirde
ID: 33513629
So your setting your staging table as VarChar(MAX) then fine tuning it when importing from the staging table (in my case) the production table?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 250 total points
ID: 33513752
If I have absolutely no idea as to what the various field lengths are that I am dealing with, yes; however, if I have a "reasonable" estimate (for instance, I have reason to believe that few US cities have names longer than 64 characters), then I base things on that reasonable estimate (e.g. InboundCity VarChar(64)).  I also tend to allow NULLs in all of the columns because, if I am dealing with CSV files (which I frequantly am), the data bay be missing.  (I'd rather have a NULL in the staging table than have an arbitrary "default value". ;-)  Invariably, though, I will slightly over estimate the size of the column, if for no other reason than the "worst case scenario" planning.
If, for instance, I bring a payment amount into a varchar column, I can better copy with things like a leading $ or embedded commas (,) or, as with one I recently had to handle, leading hyphens/negative signs.  
0
 

Author Closing Comment

by:mirde
ID: 33649678
Thanks this has helped.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Convert rows to columns 5 34
mssql 7 32
VM SQL server license. 1 67
Query Result column name from data 16 13
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question