DTS Package Excel Connection - Please Advise

Hi,

I'm using MS SQL 2000 and I want to export data from MS SQL database to MS Excel file. So, I choose the DTS package tool to do this job.

I try to create the connection to the Excel file, which located in the shared location by using the UNC path, e.g. \\servername\foldername\filename.xls However, I cannot provide the username/password to connect to the file's location. So, the connection does not work.

Do you have any idea how to create the Excel connection along with shared username/password ?

Anyway, if it is not possible :-( any advices about exporting MS SQL data to MS Excel sheet via DTS packages are still welcome....

(The reason that I prefer DTS package bec. I can schedule the job to run it automatically)

Thank you in advance.

You need not mention the user and password to the excel file, until unless you want to secure the access to excel file with a password.

KBA http://support.microsoft.com/kb/319951 for your information to perform this action without any issues.

|||

Thanks, I have tested by sharing a folder on my machine and give access to "Everyone" account. It works fine .

However, with the real situation. The shared folder, which I have to place files in it, needs authentication. They don't allow shared folder to have a read/write access for "Everyone" account.

What should I do?

|||With a READ permission those users should be able to read the file on that shared folder, this has to be done using Windows explorer and SQL will not have any influence in this case. But if you are running this as a scheduled job then SQLAGent will do the rest if it has required privileges.

DTS Package Excel Connection - Please Advise

Hi,

I'm using MS SQL 2000 and I want to export data from MS SQL

database to MS Excel file. So, I choose the DTS package tool to do

this job.

I try to create the connection to the Excel file, which located in the

shared location by using the UNC path, e.g.

\\servername\foldername\filename.xls However, I cannot provide the

username/password to connect to the file's location. So, the connection

does not work.

Do you have any idea how to create the Excel connection along with

shared username/password ?

Anyway, if it is not possible :o any advices about exporting MS SQL

data to MS Excel sheet via DTS packages are still welcome...

(The reason that I prefer DTS package bec. I can schedule the job

to run it automatically)

Thank you in advance.I am betting the user context that the dts package is executing under dopes not have access to the UNC path.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks|||Is your windows login having appropriate rights on that folder & files..?

To check try this...

1. Click Start Menu -> Run
2. Write \\servername\foldername\filename.xls -> Press OK

If the file opens, ensure that you are having appropriate rights & need to check something else.|||I do not understand that you are saying
"I try to create the connection to the Excel file, which located in the
shared location by using the UNC path, e.g.
\\servername\foldername\filename.xls However, I cannot provide the
username/password to connect to the file's location."

To create a schedule job you must be sa and DTS use SQL-Agent login to execute. What you need to do is give the SQL-Agent login the permission to access the target location \\servername\foldername\. What I mean is you do not ever pass password in DTS package to make a connection to Excel document. Please clrify.

Thanks

Thanks for all replies.

To clarify... A scheduled job that I created runs steps with TSQL command. I have a stored procedure to load and execute DTS package by database account. Since more and more securities are concerned, it is not possible for me to run the scheduled job with DTSRUN.exe via local windows account.

For more information: http://www.databasejournal.com/features/mssql/article.php/10894_1459181_3

That's why I can't identify what the SQL-Agent login is because I didn't use it to run the jobs.|||OK
You mean you are using the following sp inside the job:
either
1. using integrated security: EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.IntSecurity=1
or
2. using the current connection via SUSER_NAME() EXEC spExecutePKGGlobalVariables @.Server='MyServer',
@.PkgName='MyPackage', @.ServerPWD='xxxx', @.PkgPWD='xxxx'

But when a job is executed, specially for file access which is your case, SQL uses SQL-Agent NT login to access the file not the user login or sql login. Therfore you must know what the SQL-Agent login is and check if the SQL-Agent login has the access permission. We had lots of issues before accessing
Excel files in the network drive. Later on we found the issue came from the permission of SQL-Agent login.
So I suggest
1. You contact your SQL server administrator to inform you SQL-Agent NT login and request permission to access the file. - Best solution.
Or
2. You may create a macro in the Excel to import the data. - Hard to schedule. But I can give you hand for this.
Or
3. You may create vbscript to import the data into Excel. This, you may be able to schedule to run. Similar to #2.

DTS migration to Oracle

Hi!
Do you know any tool ou method to help the migration of an entire DW
repository in SQL Server 2000 to Oracle? And I mean the Dtata Transformation
Services too... I think it is veru difficult to migrate DTS to DML in
Oracle, but who knows there is some tool that transforms DTSs into PL/SQL (I
doubt it, but...).
thanks.
FPG
Hi Filipe,
you are out of luck as far as I am aware....there are few tools to
convert etl from one thing to another....I always recommend to my
clients to use ETL tools that can move from one database to another
with zero changes...and I liked the idea so much I wrote one for
myself...LOL!
At least if you can move your entire ETL/BI subsystem from one OS/RDBMS
platform to another you can push the vendor for a better deal on
maintenance...if you are lucky...
Best Regards
Peter Nolan
www.peternolan.com

DTS migration to Oracle

Hi!
Do you know any tool ou method to help the migration of an entire DW
repository in SQL Server 2000 to Oracle? And I mean the Dtata Transformation
Services too... I think it is veru difficult to migrate DTS to DML in
Oracle, but who knows there is some tool that transforms DTSs into PL/SQL (I
doubt it, but...).
thanks.
FPGHi Filipe,
you are out of luck as far as I am aware....there are few tools to
convert etl from one thing to another....I always recommend to my
clients to use ETL tools that can move from one database to another
with zero changes...and I liked the idea so much I wrote one for
myself...LOL!
At least if you can move your entire ETL/BI subsystem from one OS/RDBMS
platform to another you can push the vendor for a better deal on
maintenance...if you are lucky...
Best Regards
Peter Nolan
www.peternolan.com

DTS load to multiple tables

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I’m not sure if I am using the right tool for the job.I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database.Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables.There are no unique ids in the source data since it is coming from a 3^rd party and the tables are not related to others.I’ve created a DTS Package and have the beginnings of several Transform Data Tasks.Each destination table has an Identity id column, which is calculated automatically.The roadblock I’ve run into is that I can’t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key.Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together.The Transform Data Task only allows me to reference one source and one destination, not multiple destinations.I hope this makes sense.Any suggestions would be appreciated.

Here’s a simple example that may help illustrate the problem.My database is much more complex.

Input table is called parcels and each row has three columns:Address, Owner, and Legal_Description.

Output database has two tables that will receive this data:Parcel table will have the Address, Owner and Parcel_ID (auto calculated).Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.

When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured.Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.

How can I do this through DTS?Thanks for any suggestions, code snippets, or references.

Due to the complexity of this task, I would suggest maybe doing this in a .NET winforms application. Set up ODBC connections to the two databases. Now write some queries in the Access database to divide the data up appropriately. Next, write insert procedures in the SQL Server database, which return an outparameter which is the id field. To get this, in the insert proc, use the @.@.IDENTITY or the SCOPE_IDENTITY calls to get the id value of the inserted row. Capture this in the .NET application, and pass this in to the insert proc in the related table. Alternatively, keep a cache of the data mappings, maybe in a temporary table or a dataset, and do the inserts as bulk inserts. Then run an update procedure which sets the foreign key based on the database mappings in the original database. So, for example, based on the Legal_Description in the legal table, update the Parcel_Id in the legal table using the owner and address values that used to share a row with the Legal_Description. Either approach should work.

let me know if you need more guidance here. Some of the SQL database engine people, or the SSIS people may be able to point you to a DTS solution that can do this. Alternatively, you could create a DTS solution that inserts into one table, inserts into the other, then calls the update procedure as described above. There is a separate SSIS forum (the new DTS), you may be better posting the question there.

HTH

For more T-SQL tips, check out my blog: