November 16, 2007 at 11:52 am
see attached
have a DTS package with a connections patters. Just wonder if anyone seen it before.
What is it and how do you create it?
November 16, 2007 at 2:57 pm
Not sure what you're asking exactly, but it looks like the package is deleting from some source and then reloading that source from data in an Access database.
The "Delete from Table..." is the delete, probably from some source table, the "Connection 1" is an Access database connection, and the "Connection 2" is a target connection which I'm guessing is SQL Server, and there's a transformation between the Access database and the final target that's doing something, which I'm guessing is loading data from the Access database into the target.
The "Connection 1" and "Connection 3" are Microsoft Access connections (created by using the "key" icon in the "Connections" section of the toolbar). To see the actual database that the Access connection is using, you can either double-click the connection or, if that errors on you, do a "disconnected edit" and view the properties of the connection object you're interested in to see the path to the Access database it's trying to point to.
Does this answer your question? If not then we need more details as to what, exactly, you're looking for. If you want help editing or creating packages like this then there are numerous resources that you can find online, I'll throw some your way if you're interested.
November 19, 2007 at 8:56 am
I would also guess that the package was created using the Import/Export wizard. It names connections "Connection 1" , "Connection 2", etc. and it creates an Execute SQL task to delete from the destination table when you check that option.
Greg
Greg
December 4, 2007 at 9:27 pm
I now know what the package does. Deletes the table and copies the Access record to SQL server table.
New question.
The way Connection Properties access to this file. This access file is on a different server. How could the DTS connection be able to connect to this access file. I know there has to be an option somewhere but I can't find it. Anyone know what I'm talking about.
December 5, 2007 at 8:15 am
To change it manually, double click on each access connection and click on the ... button. Browse to the new database location and clikc OK, OK.
Do you want to be able to change it when the package is run, dynamically?
Norman
DTS Package Search
December 5, 2007 at 8:48 am
You should also use a UNC path rather than a mapped drive letter.
Greg
Greg
December 5, 2007 at 2:10 pm
nite_eagle (12/5/2007)
To change it manually, double click on each access connection and click on the ... button. Browse to the new database location and clikc OK, OK.Do you want to be able to change it when the package is run, dynamically?
Norman
DTS Package Search
Sorry, but I know the browse button. The problem is this file is on a different server. That means you can't find it when you browse it.
December 5, 2007 at 2:13 pm
Greg Charles (12/5/2007)
You should also use a UNC path rather than a mapped drive letter.Greg
It's not a mapped drive as I can't see it. I do have sys admin rights.
December 6, 2007 at 9:20 am
Since you are an admin use \\server\c$\dir\dir\access.mdb or what ever your path is.
But that's not really the best way. Mapped drive will work from you login on your workstation, but not as a scheduled job from sql server.
Better to create a share on the directory on the server where the access mdbs are stored.
Then you can use \\server\share or what ever you name the share.
Norman
DTS Package Search
December 6, 2007 at 2:34 pm
nite_eagle (12/6/2007)
But that's not really the best way. Mapped drive will work from you login on your workstation, but not as a scheduled job from sql server.
Better to create a share on the directory on the server where the access mdbs are stored.
Then you can use \\server\share or what ever you name the share.
Norman
DTS Package Search
http://www.dtspackagesearch.com/%5B/quote%5D
Isn't it
\\server\c$\dir\dir\access.mdb and \\server\share the same?,
You said it's not really the best wayt then you said better to create a share...
December 6, 2007 at 2:59 pm
Using \\..... as the path works only when I run the DTS package manually.
If I set up a SQL job to call this DTS package. It fails.
Anyone could help me solve this?
December 7, 2007 at 7:20 am
They are the same in that they are UNC paths but not as far as security goes.
The C$, D$... drive$ are the administrative shares and the account that accesses them must?/should be local administrator.
My environment does not allow the sql server service accounts to be local admin and they run under domain accounts not local system.
Therefore creating the public share is the "secure" way as the accounts the need access can be granted the appropriate rights.
Norman
DTS Package Search
December 7, 2007 at 7:23 am
i have found out it's the same too. I believe the sql job doesnt allow UNC PATH as I tried with a single txt file and still can't find the file
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply