csvimportsql server
Table created
Data in flat file loaded to the table but with errors. please advise how to fix it
(a) 'address' is split to 3 columns causing mismatch between the source and destination
(b) 3 Nulls in the 'B_Date' column due to different format. Do I need to massage the raw file before load into the Microsoft SQL or what is the SQL formula?
You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.
Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.
With only that change, the package will execute successfully
Also, an excellent answer from SO on the same issue
In FoxPro, under the Wizard menu, choose "upsizing". From there, choose an ODBC connection to the SQL Server database and step through it. Also, there is a much improved upsizing Visual FoxPro project that you can download here.
I have had trouble upsizing to SQL Server 2008 and above so if you have a SQL Server 2005 database around, you might have more success upsizing to it and migrating your tables to there final destination.
Best Answer
Why do you think address is being split into three columns? Based on your screenshot column 5 is being mapped to address and 6 and 7 aren’t being mapped to anything.
Can you please share the full error messages? Also does your file have headers, I presume not based on your screenshot. Try adding headers to the flat file and letting the wizard create the destination table for you.