DTS transformation from text file to SQL Server

I have created a DTS package that relies on a Text Input file as the source. The output is a SQL Server database/dataset.

The text input file is referenced via a file DSN. In the file DSN I have outlined the directory, format and that it’s Fixed Length. I have also defined all the fields, sizes and type.

Now in the DTS Connection properties task, Connection1 I point to the file DSN. Then I create a second DTS Connection, Connection2,  properties task which references the output SQL Server database/dataset. I link the two Connection properties with a data transformation pipeline.

Now here is the problem. If I right-click on the data transformation pipeline then select properties then select transformations I get this SQL Error:

HResult of 0X80040e09 (-2147217911) returned. Unexpected error occurred. An error result was returned without an error message.

Why am I getting this problem ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You need to specify your source selection first. But before you do that why are you using a DSN for the text file? Why not just use that as the source in DTS and the SQL Server or what you are using as a destination. Then set the fixed length columns right in the package. It would be alot easier to maintain.  
ejr19592004Author Commented:
That's the way it was set up initially. But I need the source file location and name to be independent of the DTS settings. File DSN is one way. I tried initially to do it via the DTS objects in VB and couldn't find a way to alter the file name and location.

Agree with michaelSFuller--get rid of the DSN and let DTS handle it--just add a dynamic properties task to the DTS package.  Set it up to point to your text file name and then you can pass it as a global parm on the DTSRun command line.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ejr19592004Author Commented:
Ok let’s go through the steps:

1.      I drag out a DPT task and call it Test
2.      Which then takes me to the DPT properties window
3.      I select Connection 1 which is my text file and then point to the Data Source property which has the directory\filename
4.      I click on set
5.      Which then takes me to Add/Edit Assignment
6.      I select global variable from the drop down list
7.      Create/Select a variable name of in_loc_file click refresh and then watch the Preview window populate with the Data Source value
8.      Click Ok.

Now I have a Global Variable called in_file_loc which is set to the Data Source for the file.

Now let’s jump to the VB code

DTSGlobalVariables("in_file_loc").Value = "c:\dir\fillename"

And then finally

Dim dtsp As New DTS.Package
   dtsp.LoadFromSQLServer _
    ServerName:=ServerName, _
    ServerUserName:=UserName, _
    ServerPassword:=UserPass, _

Because if that’s what you are thinking I couldn’t get this to work on account of the fact that VB kept complaining that DTSGlobalVariables was not defined even though it is referenced in the Project References.

Any more ideas ?
Hmmm, didn't say anything about VB in your question.

You're not using the object model correctly--there is a good example here:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ejr19592004Author Commented:
Sorry arbert but it started out as a VB/SQL Sever/DTS post and then go morphed into a VB/DTS/DSN issue. The page you sent looks really good. I will come back to you in an hour or so if you are still awake.

My colleagues are badgering me to go to lunch. It's 12:30 PM in Sydney.

I will continue after lunch.

Sydney, just got back from there--wish I were still enjoying th warm weather!

ejr19592004Author Commented:
Warm weather ? It's been raining all week. This is the first day that I get to leave my brolly back home.

We are getting into the tropical season here.

Anyho. I am back from din-dins so I am going to get stuck into that most excellent page link you sent across.

Back to you soon.

ejr19592004Author Commented:
Arbert. That's the ticket. Thanks Mate.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.