Issue with SSIS on flat files to tables with fixed position

I have a couple of questions about the task on which I am stuck and any answer would be greatly appreciated.

I have to extract data from a flat file (CSV) as an input and load the data into the destination table with a specific format based on position.

For example, if I have order_id,Total_sales,Date_Ordered with som开发者_Go百科e data in it, I have to extract the data and load it in a table like so:

• The first field has a fixed length of 2 with numeric as a datatype.

• total_sales is inserted into the column of total_sales in the table with a numeric datatype and length 10.

• date as datetime in a format which would be different than that of the flat file, like ccyy-mm-dd.hh.mm.ss.xxxxxxxx (here x has to be filled up with zeros).

Maybe I don't have the right idea to solve this - any solution would be appreciated.

I have tried using the following ways:

• Used a flat file source to get the CSV file and then gave it as an input to OLE DB destination with a table of fixed data types created. The problem here is that the columns are loaded, but I have to fill them up with zeros in case the date when it is been loaded or in most of the columns if I am not utilizing the total length then it has to preceded with zeros in it.

  For example, if I have an Orderid of length 4 and in the flat file I have an order id like 201 then it has to be changed to 0201 when it is loaded in the table.

• I also tried another way of using a flat file source and created a variable which takes the entire row as an input and tried to separate it with derived columns. I was to an extent successful in getting it, but at last the data type in the derived column got fixed to Boolean type explicitly, which I am not able to change to the data type I want.

Please give me some suggestions on how to handle this issue...

Assuming you have a csv file in the following format

order_id,Total_sales,Date_Ordered
1,123.23,01/01/2010
2,242.20,02/01/2010
3,34.23,3/01/2010
4,9032.23,19/01/2010

I would start by creating a Flat File Source (inside a Data Flow Task), but rather than having it fixed width, set the format to Delimited. Tick the Column names in the first data row. On the column tab, make sure row delimiter is set to "{CR}{LF}" and column delimiter is set to "Comma(,)". Finally, on the Advanced tab, set the data types of each column to integer, decimal and date.

You mention that you want to pad the numeric data types with leading zero's when storing them in the database. Numeric data types in databases tend not to hold leading zero's. So you have two options; either hold the data as the type they are in the target system (int, decimal and dateTime) or use the Derived Column control to convert them to strings. If you decide to store them as strings, adding an expression like

 "00000" + (DT_WSTR, 5) [order_id]

to the Derived Column control will add up to 5 leading zeros to order id (don't forget to set the data type length to 5) and would result in an order id of "00001"

Create your target within a Data Flow Destination and make the table/field mappings accordingly (or let SSIS create a new table / mappings for you).