October 28, 2008 at 9:38 am
Last night we had to rebuild a small database because some tables where causing problems. There were two tables that we needed to save off so that we could get our specifications back. My plan was to just export the data from those tables to a flat file and then do the import task from the flat file back to those two tables once we had recreated the database.
The export process was simple and I just took the defaults (no headers, comma delimited columns, CRLF row delimiter, no text qualifier). I did make sure there were no commas in the data being exported.
We have done these same steps before in SQL 2000 and it was a simple import of the data we had exported. We never had problems doing the import. Now in SQL 2005 we had to struggle to get the import of two small flat files. Since we just took the defaults on export, I was anticipating just taking the defaults on the import and in theory we should have been fine.
The problem seems to occur due to the Advanced area where a column data type and output column width needs to be specified. The system defaulted all the columns to a string with a width of 50. Some of the table columns were shorter and some were longer than that value. In the end I had to set each output column of the flat file to match the data type and width of the table columns.
Why should we have to do that? Really, if the data goes beyond the length of the table column, I would expect the import process to blow up; but I shouldn't have to define all of that up front. Again, in my mind it should be "click-click-click" and we're done.
Has anyone else encountered this or is there an easier way that I'm not aware of in SQL 2005?
Thanks for hearing my rant.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
October 28, 2008 at 5:38 pm
Yeah i have run into a similar problem. It does a decent job auto scaling columns down from 50 but it seems to be the normal default. when I have to export data and want to maintain the data types and sizes I prefer to use some other platforms tables. DB4 works well for that purpose since it supports varchars, access or even excel aren't bad if you don't mind converting to and from unicode. flat files are pretty dump and MS has a ways to go to improve the flat connection to make it more user friend and intuitive. I am starting to pilot 2008, maybe there will be some surprise improvements in there, but i hadn't heard about any regarding this.
Eric Sloan
DBA
October 29, 2008 at 7:56 am
Yesterday I just ended up creating and testing a series of good old simple batch files: one using BCP.exe to export my tables; the other batch file uses SQLCMD.EXE to make sure everything was cleaned up and then BCP.exe again to import everything back to the database tables.
This will be much simpler for future times when we have to do these steps.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
November 3, 2008 at 8:24 pm
I agree. The 20 year old bcp program is far easier to use for this type of simple process than the new fangled SSIS. It would have helped some if you had saved the column names with the data, but not that much. You still would have had all the length and data type issues. The problem really seems to be that the meta data assoiciated with the flat file import is derived from the flat file it self.
This could be corrected, if there was some kind of option to retreive the meta data from the table you are importing into instead. The flat file conection would require both the existing file connection and a new DB connection with a table name to retrieve the meta data with the proper types. The DB connection would only be needed at design time. The other option would be to allow it to read something like a BCP .fmt file. BCP can generate these file on export so that they can be used later to import data.
They may have improved the efficiency and flexability. They have definitely not made SSIS easier and faster to use than DTS or BCP.
February 21, 2018 at 3:30 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply