Question about OLE DB conn mgrs

Hi,

I just realized I have this question. Let's say you are building a simple package that just loads some data from a flat file into a database table.

And let's say that many people will be logging on to the server to run this package from the file system, (so each person will have their own Window's login.) Some people will have rights to read/write to tables database, but some will not. They just need to be able to run the package.

So, when you set up the OLE DB Destination conn mgr, should you choose Windows Authentication or SQL Server login?

Because what if someone does not have permission to write to the database? Won't the package fail to execute since it's relying on the Window user's login to for the OLE DB conn mgr?

Thanks

Why not schedule the job via SQL Server Agent, and then give the users permissions to execute the Agent job? Then you can use whichever login method you like.|||

My question is more hypothetical, as I am just trying to understand how Windows login works vs. SQL Server login in a package.

But from your answer it seems that my assumption is correct, in that the OLE DB conn mgr does rely on the the window user's login to run the package? And if the permission isn't there, no dice.

|||

sadie519590 wrote:

My question is more hypothetical, as I am just trying to understand how Windows login works vs. SQL Server login in a package.

But from your answer it seems that my assumption is correct, in that the OLE DB conn mgr does rely on the the window user's login to run the package? And if the permission isn't there, no dice.

Correct. That's what Windows Authentication is. It passes your logged-in user credentials on to SQL Server. So yes, if the logged-in user account doesn't have that same account setup in SQL Server, no dice.

SQL Server authentication is specifically set inside SQL Server. A user account, local to SQL Server is created. You can setup the package to use SQL Server authentication, and as long as you have the protection level set to EncryptSensitiveWithPassword or similar (don't use UserKey), you should be okay using that.|||

Ok, thanks. I just wanted to make sure my thinking was correct.

But back to your original answer, how would one schedule a file system package in a job - using DTExec? Would you put the password in a config file?

Also, how do you give permission to run an agent job?

Thanks

|||

sadie519590 wrote:

Ok, thanks. I just wanted to make sure my thinking was correct.

But back to your original answer, how would one schedule a file system package in a job - using DTExec? Would you put the password in a config file?

Also, how do you give permission to run an agent job?

Thanks

When you create an Agent Job, you can specify the location of the package. If you use EncryptSensitiveWithPassword, you can alter the command line that Agent uses to issue the /DECRYPT flag. You don't necessarily have to worry about *how* to run the package.

Try it. Just try creating an Agent job and walk yourself through it.

Giving permission to an Agent job is the role of the SQL Server DBA. Are you this person?|||

Thanks. I'm sure it's very straightforward.

I have full permissions in the db, just not sure how to assign someone the permission to run a job. Is it done a job level? I've never set this permission as I only work with scheduled jobs.

|||

sadie519590 wrote:

Thanks. I'm sure it's very straightforward.

I have full permissions in the db, just not sure how to assign someone the permission to run a job. Is it done a job level? I've never set this permission as I only work with scheduled jobs.

You'll need to read on Agent jobs and security. There are three roles that you can use out of the box to specify security permissions for a given user.sql

Question about Excel source - how to modify columns?

Hi,

I have a package that uses an Excel file source. There appears to be no place to modify the column data types as you can with a flat file manager. As such, the source columns do not match the columns in the database.

I believe I must be overlooking something here.

Can someone please tell me how I can modify the Excel column datatypes?

Thanks

nevermind

found it under "advanced editor", "input and output" properties

|||

Well, I'm having a difficult time changing the datatypes on my excel columns.

The "input and output properties" allow you to change the datatypes of the "output columns", but if I try to modify the "external columns", I get an error that the source column doesn't match or something to that effect. If I just try to change the length, it gives me other errors. So it seems I can't really change these?

So I left all the "external" columns as unicode strings with the original length, and changed the "output columns" to regular strings and the floats to numerics.

But in the end, this has solved nothing because I'm still getting truncation errors. So obviously changing the "output columns" isn't enough.

I really don't think I'm doing this right! Need help.

|||Sadie,
Use a derived column or a data conversion component to change your data types. Let the Excel manager and Excel Source do their jobs. The output data type is dictated by the driver and the data in the Excel sheet.|||You need to use a derived column or data conversion transform to change the data types. Excel data is stored as unicode, and it is a conversion step to change it to ANSI. Same thing for the floats.|||

Ok, duh

makes sense... just never worked with an excel spreadsheet before