Hi All, I am recently working on CSV files as input.I see the default datatype as [dt_str][50] for all the columns.Even though my sql server destination table has different datatypes I am successfully loading data without changing the datatypes at the source or without using datatype conversion in the middle before loading data. Is it really necessary to change datatypes of columns?because I dint face any problems loading data with having datatype at the source as dt_str(50).and one more thing when we open csv files using excel we see both blanks and nulls as blanks(empty spaces.how can we differentiate blanks and nulls.how important is checking the option "keep nulls in the source"

First of all, LUCKY YOU that your Flat File Source data ALWAYS meets the data type requirement of the destination! That happens maybe once in a thousand times for me. QUICK, Play the Lottery tonight! You're on a LUCKY streak! But seriously, sometimes you get data that is clean in that way and SSIS can do an implicit conversion to number or date types ("12345" can easily be converted to a number even though it is presented as DT_STR, and so can "Jan 3, 1996" be converted to a Date). But typically, you would put in Data Conversion and Derived Columns to 'scrub' the data into what you want and follow the rules you set forth for what to do with data that doesn't follow the rules. It's the "T" part of ETL = "Transform" Next, regarding the NULLs, what are the Business Requirements? Is a NULL just as good as an empty string? If not, you may need to do some conversions. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks for your reply Todd. May be I am not so lucky as you are saying that I would have learned much more than I know presently if I had faced any errors.anyways,ideally we should make sure that what type of data we are getting from the source and need to change datatypes to map destination right? do you think did I overlook any errors or datachanges because of not mapping datatypes with the dastination and some how I din't notice because I haven't get any errors? Is it better to change datatypes at the source only or in the middle by using dataconversion in the middle? can you give me one example of getting error because of not mapping the datatypes with the destination?

Personally, I prefer taking flat files and loading them into the Data Flow pipeline at DT_STR (or DT_WSTR) then attempting to scrub them in SSIS. Suppose you have a data column and the contents of row 128,458 shows the date as "Foo-Bar-uary 30, 19999". Now, maybe a human brain could interpret that as 2/29/1999 but a computer is much more literal than a brain. If you specify Date type in your Flat File, you will get an error and a big red box on your Data Flow because SSIS doesn't know how to 'fix' that date. If you try to find the offending record in Notepad or Excel, you'll have a tough time. But if you load that into a String column, you can send it through a Data Conversion transform, and tell SSIS to re-direct the rows that can't be converted to the Error Output where they can be a) assigned defualt values then merged back into the main flow, or b) counted and/or ignored, or c) logged to an error (flat) file for later inspection. I can't answer what is best because to do that I would have to be a mind reader. Only you (and your Business Requirements) call figure that out.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.