I have just converted an Access application to SQL Server 2005 with no
problems. Now that everything is working fine I am looking to create a DTS
Package to convert data from existing Access Databases to my SQL 2005
Database. I see now that DTS is a LEGACY thing and only allows you to
import/migrate old SQL 2000 DTS Packages. I was familar with SQL 2000 DTS
tools and comfortable with them. Now, I'm finding myself very frustrated,
because I'm not using, nor ever have used, SQL 2000, so I have nothing to
migrate. I see the replacement is something called "SQL Server Integration
Services".

I've been able to find "SQL Server Integration Services", but cannot get
past the "Credential Name", of which I have no idea what that is. So, bang,
I'm stuck. I've also found an "Import Data" opton from the "Tasks" listing
and that is of no use either as it appears to copy identical databases from
one to another. That's useless.

In SQL 2000 building a DTS didn't require much knowledge and effort to get
up and running with and now I find myself completely lost. Is this SQLSIS
anything like the old DTS or what? All I want to do is import data form
Access to SQL Server and they are not carbon copies of eachother either. So I
have create custom conversion scripts to handle each table. I was under the
impression that SQL 2005 was so much better and as of now I don't see that to
be the case.

I'd appreciate some assistance with this if possible, because as of now my
only option is to write an import utility that runs all form VB Code, of
which was not my plan. This will just put my project way behind schedule.

Thanks in advance for any help anyone can provide.

Hi Greg, I too have found that SSIS has a steep learning curve. Hang in
there.

Here are some suggestions.
1) Create a SQL 2000 DTS package run it from SSIS.
2) Export the Access data to a CSV file and then import using SQL 2005 SSIS.
3) Get the Data into SQL 2005 using any means possible even if the tables
aren't in the form that you want and then move the data from there to the
tables that you actually wanted them in, in the first place.
4) Send us some more discrete examples of problems you are having.

Is this a one time thing or an on going task that needs to be automated?

I am not sure what the "Credential Name" thing is either as I have never
seen this. Do you see this in the Connection Manager? More information is
helpful.

--
Thank you,
John


"Greg" wrote:

I managed to get past the "Credential Name" aspect. Had something to do with
my Account Name or Account Access.

If I had SQL Server 2000 installed I would just go ahead and create my DTS
in SQL 2000 as you suggested and then bring it into SQL 2005. Problem with
that solution is I don't have SQL 2000. I did not realize I would need SQL
2000 to use SQL 2005 and MS did not lead on to that DTS was being trashed.
DTS was so easy to learn that an idiot could use it, but now SQL 2005 leaves
me in the datk. Seems to me taht things should get easier, not harder.

Anyway, what I've given into is writing my import routine via code because
there is so much data that must be normalized in the process. Plus, within
Code I have a lot more control it seems. I'm a little sad having to do this
though because I wanted to work a lot more with SQL Server 2005 and not
Visual Basic.

This is a one time import a customer will run to import data from one of our
older MS Access databases to SQL Server 2005 version So, there is no reuse
for the module and it does not matter a great deal that it be 100% efficient
in terms of speed, etc.

Thanks for your suggestions, but at this time I will just go with what I'm
doing as it is allowing me to move forward.

"John A" wrote:

Hi,
You can import your Access database in SQL Server Management Studio via:
Creating a new database for data transfer, right clicking the database,
selecting Tasks, selecting Import Data..., and following the wizard to
carry out the next steps as well as Import/Export wizard in SQL Server 2000.

Unfortunately SSIS hasn't integrated this function to its IDE.

I would like your feedback to Microsoft so that this funcation can be
included in SSIS in the next release.
You can give Microsoft feedback via:
http://connect.microsoft.com/feedbac...aspx?SiteID=68
Note: please logon first before submitting a feedback.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Sincerely,
Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====