MS SQL 2005 DTS Trouble...

Expand Post »

(And before anyone points it out; I know they're not calling it DTS any more!)

My problem is as follows: Whenever I go to import a flat file using the DTS import it's setting the default column sizes to be ludicrously small (50 characters) and it doesn't subsequently matter HOW I define the table I'm pulling the data into, it chokes until I correct it.

One (obvious) way round this would be to save the package and go from there. Unfortunately, because of the nature of the files, this isn't all that practical (because this is going to be an issue across a good 10 or 15 different databases).

Is there a way of getting SQL to up its default settings so that I can go back to controlling column size on the table only? Or am I SOL?

Similar Threads

MS SQL 2005 Developer Help in MS SQL
Link server in MS SQL 2005 in MS SQL
SQL 2000 and SQL 2005 server on Single server in MS SQL
save GDI+ image into sql 2005 database in C#
SQL 2005 upgrade questions in MS SQL

Athersgeo

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

11 posts
since Aug 2005

Permalink

Jun 23rd, 2009

Re: MS SQL 2005 DTS Trouble...

I'm pretty sure you're SOL. I do a lot of DTS intensive work and I have clients install SQL 2000 so i can use DTS to import data destined for SQL 2005. I don't like what they did to DTS either.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Jul 4th, 2009

Re: MS SQL 2005 DTS Trouble...

Hi Athersgeo,

Believe it or not, this is a feature of SSIS. The best way to handle flat file importing is mapping the flat file connection EXACTLY the way the file is mapped and configuring the source to perform how you want it to when anything different presents itself (failure on truncation etc).

You can easily fix this issue by mapping the file connection and updating the metadata within your dataflow. Once the file uses the correct metadata it will update the rest of the respective tasks that deal with that data. Sometimes I don't like doing it by clicking on the task in question and choosing update, so most times I just re-drag and drop the task flows (the little green and red arrows).

Hope this helps!

cutepinkbunnies

Reputation Points: 15

Solved Threads: 9

Junior Poster

Offline

143 posts
since Apr 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: The INSERT statement conflicted with the FOREIGN KEY constraint

Next Thread in MS SQL Forum Timeline: Generate Alpha-Numeric Values in SQL SERVER

DaniWeb Message

Cancel Changes

User Name
Password