TSQL or SSIS

Question

TSQL or SSIS

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
July 28, 2010 at 12:04 pm

#238145

Hi,
I have a requirement where i need to update a SQL table
myTable from a text file myText.txt.
Request is something like this:
update myTable
set myTable.isAutomatic = myText.isAutomatic
where
myTable.Col1 = myText.Col1
and myTable.Col2 = myText.Col2
and myTable.Col3 <> 'XXX'
if no match is found, then log Col1 in a logTable.
I have been told to do it as an SSIS package and schedule it to run as a job.
However i found this article
http://qa.sqlservercentral.com/articles/OpenDataSource/61552/
and I am willing to do this inside a stored procedure using
OpenDataSource method which i found very useful and simple.
For this method to use, we need to enable 'Ad Hoc Distributed Queries'
Is there any vulnerability using this approach.
Is it prone to be Hacked?
(I doubt that our security dept will Enable this method.)
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

If the structure of the text file is known, BULK INSERT would probably be your best bet. Doesn't require ad hoc queries to be enabled.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

SSIS does not require that you enable ad hoc queries like that. OpenDataSource is not required in SSIS either.

You would create a data connection manager specifying a flat file source. Then you can specify the destination in the flow of the package.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

I have an aversion to directly manipulating data in my database from another source such as a text file.

I would use Jeff's suggestion and bulk insert from the text file into a staging table and do the update from there. That way you have a better chance to apply business rules to your update.

Todd Fifield

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Jeff Moden (7/28/2010)
If the structure of the text file is known, BULK INSERT would probably be your best bet. Doesn't require ad hoc queries to be enabled.

Thank you.

I dono why our organization doesn't grant even BULK INSERT permissions too 🙁

They have upgraded from SQL 2000 to 2005 few years back and now for every operation like TRACE,BULK INSERT,DDL,etc requires separate permissions. As they require separate permission they are not ready to grant them... !!!

Is there any drawback of BULK INSERT?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

KBSK (8/1/2010)
Jeff Moden (7/28/2010)
If the structure of the text file is known, BULK INSERT would probably be your best bet. Doesn't require ad hoc queries to be enabled.
Thank you.
I dono why our organization doesn't grant even BULK INSERT permissions too 🙁
They have upgraded from SQL 2000 to 2005 few years back and now for every operation like TRACE,BULK INSERT,DDL,etc requires separate permissions. As they require separate permission they are not ready to grant them... !!!
Is there any drawback of BULK INSERT?
Thanks,
KB

It may be that they're using Windows Authentication and they don't want to give everyone in the group those kind of privs. Neither do they want to maintain yet another group. They may also think that being able to BULK INSERT requires ALTER TABLE privs, as well. That's not true if you use a proper staging table as the target of the BULK INSERT.

As a side bar, it's amazing to me that folks will give others access to the "world" using SSIS for ETL processes but won't even think of setting up an ETL account or special ETL group to run BULK INSERT even though it's the fastest and one of the easiest ways to move data into the system, bar none. Either that or they're still equating the requirements of OPENROWSET with those of BULK INSERT (and they're quite different like I said before).

It's sometimes difficult to "fight city hall" and you may have to kowtow to their wishes by using SSIS... in which case, I can't help. I normally don't have to "fight city hall" on such things because I "am city hall" in most places I end up working. I'm afraid that you may have to hit the books on SSIS for this. My understanding is that it's not a difficult process (although you couldn't prove it by me according to all the posts requesting such a thing :-P).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 6

Jeff Moden (8/2/2010)
That's not true if you use a proper staging table as the target of the BULK INSERT.

Yes, its a Windows Authentication(for every one).

Could you please clarify on staging table?

Is staging table a Physical table in the database?

or

It can be a temp table or a global temp table too?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

A "staging table" can be a real table (usually not recommended but ok sometimes, won't help you with privs, though), a Temp Table, a Global Temp Table (NOT RECOMMENDED), or a Table Variable (ALSO, NOT RECOMMENDED).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

Jeff Moden (8/2/2010)
It's sometimes difficult to "fight city hall" and you may have to kowtow to their wishes by using SSIS... in which case, I can't help. I normally don't have to "fight city hall" on such things because I "am city hall" in most places I end up working.

I managed to "fight city hall" 🙂

in one or two days I will be having BULK INSERT permission.

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 9

KBSK (8/4/2010)
Jeff Moden (8/2/2010)
It's sometimes difficult to "fight city hall" and you may have to kowtow to their wishes by using SSIS... in which case, I can't help. I normally don't have to "fight city hall" on such things because I "am city hall" in most places I end up working.
I managed to "fight city hall" 🙂
in one or two days I will be having BULK INSERT permission.
Thanks,
KB

Hi,

I managed to get the BULK INSERT permission and ran into some other error.

1st error:

Cannot bulk load because the file "C:\TEST\test9064.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

2nd error after giving network path:

Cannot bulk load because the file "\\[edited_path]\TEST\test9064.txt" could not be opened. Operating system error code 5(Access is denied.).

I am using SQL Server 2005 and connecting to a XXX09 server.

Could you please let me know what and where the R/W access to be granted?

I mean, do the server XXX09 must have the R/W permission to the folder that I am accessing?

Note: I do not have access to the server C:\ drive

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

KBSK (8/5/2010)
KBSK (8/4/2010)
Jeff Moden (8/2/2010)
It's sometimes difficult to "fight city hall" and you may have to kowtow to their wishes by using SSIS... in which case, I can't help. I normally don't have to "fight city hall" on such things because I "am city hall" in most places I end up working.
I managed to "fight city hall" 🙂
in one or two days I will be having BULK INSERT permission.
Thanks,
KB
Hi,
I managed to get the BULK INSERT permission and ran into some other error.
1st error:
Cannot bulk load because the file "C:\TEST\test9064.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).
2nd error after giving network path:
Cannot bulk load because the file "\\[edited_path]\TEST\test9064.txt" could not be opened. Operating system error code 5(Access is denied.).
I am using SQL Server 2005 and connecting to a XXX09 server.
Could you please let me know what and where the R/W access to be granted?
I mean, do the server XXX09 must have the R/W permission to the folder that I am accessing?
Note: I do not have access to the server C:\ drive
Thanks,
KB

The login that the SQL Service uses must be able to see the path and read it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

liesl.fourie Valued Member Points: 70 More actions · Answer 11

Your problem could be what is referred to as the "Double hop" error: User logs into SQL with Windows Authentication.

File to import resides on network machine (UNC path).

Problem occurs because SQL machine does not have right to pass user details through to file server.

You have a few options:

Solution A: Use SQL Server Authentication.

Solution B: Copy files to SQL Server server and BULK INSERT from there.

Solution C: Set up Delegation/Impersonation. (See http://kendalvandyke.blogspot.com/2008/11/delegation-what-it-is-and-how-to-set-it.html)

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 12

Jeff Moden (8/5/2010)
KBSK (8/5/2010)
KBSK (8/4/2010)
Jeff Moden (8/2/2010)
It's sometimes difficult to "fight city hall" and you may have to kowtow to their wishes by using SSIS... in which case, I can't help. I normally don't have to "fight city hall" on such things because I "am city hall" in most places I end up working.
I managed to "fight city hall" 🙂
in one or two days I will be having BULK INSERT permission.
Thanks,
KB
Hi,
I managed to get the BULK INSERT permission and ran into some other error.
1st error:
Cannot bulk load because the file "C:\TEST\test9064.txt" could not be opened. Operating system error code 3(The system cannot find the path specified.).
2nd error after giving network path:
Cannot bulk load because the file "\\[edited_path]\TEST\test9064.txt" could not be opened. Operating system error code 5(Access is denied.).
I am using SQL Server 2005 and connecting to a XXX09 server.
Could you please let me know what and where the R/W access to be granted?
I mean, do the server XXX09 must have the R/W permission to the folder that I am accessing?
Note: I do not have access to the server C:\ drive
Thanks,
KB
The login that the SQL Service uses must be able to see the path and read it.

When running BULK INSERT, it runs under the SQL server's network login ID and not under the client's trusted connection. Is this correct?

So if I run the script from other machine, it's the SERVER that needs file access and Not the machine from which i execute. Is this Correct?

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

liesl.fourie Valued Member Points: 70 More actions · Answer 13

Here is how I understand it. When you use Windows authentication to connect to SQL Server, SQL Server tries to pass those credentials on to the file server and use those credentials (client) to access the file.

Since this is not allowed in SQL Server 2005 (this was seen as a risk), you need to set up delegation for that account to allow SQL Server to pass the credentials to another machine. (see link in previous post)

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 14

liesl.fourie (8/5/2010)
Your problem could be what is referred to as the "Double hop" error: User logs into SQL with Windows Authentication.
File to import resides on network machine (UNC path).
Problem occurs because SQL machine does not have right to pass user details through to file server.
You have a few options:
Solution A: Use SQL Server Authentication.
Solution B: Copy files to SQL Server server and BULK INSERT from there.
Solution C: Set up Delegation/Impersonation. (See http://kendalvandyke.blogspot.com/2008/11/delegation-what-it-is-and-how-to-set-it.html)

hi,

Solution A did not work. I had the SQL Server authentication and i tried bulk insert and got the same error Access Denied.

Solution B: We will not get access to Server 🙁

Solution C: It looks like a complicated process and definitely they will reject 🙁

its better i would've gone through SSIS approach...

Now i got a bad impression 🙁

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!