Unanswered: SSIS issue with Flat Files to Table with fixed position
I do have couple of questions on the task which I trying to break my head and it would be greatly appreciated for any answer:
I do have a flat file(csv) which comes has a input and I have to extract the data from the csv file and load into the destination table with a specific format based on position. For example if I have order_id,Total_sales,Date_Ordered with some data in it. I have to extract the data and load it in a table which has a format in which for the first field of fixed length of 2 with numeric as a datatype and total_sales into the column of total_sales in the table with numeric of length 10 and then date as datatime in 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).
May be I am not getting it in 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 has an input to OLE db destination with a table of fixed data types created. But the problem here is that the columns are loaded but I have to fill up with zeros in case of 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 a 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 total 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 which i would want to.
Please give some suggestions on how to handle this issue..
varun 2003, I would just take the data you have in the CSV and get it into a table (Columns could all be Varchar). The use SQL (or a program / procedure if too complex) to manipulate the data as needed and put it where ever you like.