December 9, 2009 at 2:49 pm
Ok, here's my issue I need to import an xml file into SQL, I tested everything out using Bulk Insert to my local SQL Server which worked great, however the application and files that I will be using is on a different PC then the Production SQL server.
I have tried all the suggestions in regards to permissions an none of them seem to work..
So my question, what are my options here?
Any assistance would be greatly welcomed.
Terry
December 10, 2009 at 12:32 am
You have to run the SQL server instance under an account which has access and appropriate rights to the remote PC.
If you will run it under such account, then you should be able to normally access the files on remote PC if appropriate sharing was set on the remote PC.
To access remote files, you cannot run the service under LOCAL SYSTEM, NETWORK SERVICE, LOCAL SERVICE or such accounts.
December 10, 2009 at 5:53 am
You should also use an SSIS package for things like this. Especially when accessing another box. You can easily include some things in the package to check if this particular file has already been pumped in. If so, do not continue, or send out an email message. You can also take advantage of the use of an audit table. This is a huge help when you need to store the history about all the steps in the package. When the package did not fire off, when the package did fire off, number of rows inserted each time. But most importantly, did the data pump actually work correctly. You can log each time the package fires and have a great log table to verify what happened each time.
Plus, it is easy to modify the package if you need. The package will be secure, you can schedule the package, or fire it off manually at any time, even from a web page.
Andrew SQLDBA
December 10, 2009 at 6:14 am
Pavel Pawlowski
"You have to run the SQL server instance under an account which has access and appropriate rights to the remote PC.
If you will run it under such account, then you should be able to normally access the files on remote PC if appropriate sharing was set on the remote PC.
To access remote files, you cannot run the service under LOCAL SYSTEM, NETWORK SERVICE, LOCAL SERVICE or such accounts."
Pavel,
The sql server instance is running under the administrator account, like i stated i have tried all of the permission options, I have given the rights to the folder and share to the administrator account and I am still getting the "Cannot bulk load because the file "\\Dev09\IMPORT\IMPORT\AO-SUMM_12072009.xml" could not be opened. Operating system error code 5(Access is denied.)." error.
Any additional thoughts?
Any assistance would be greatly welcomed.
Terry
December 10, 2009 at 6:19 am
"You should also use an SSIS package for things like this."
Andrew,
There's the problem I have 4 total imports that need to be brought in, so what I have is a vb.net application that downloads a zip file from the web, unzips the zip file and then I was wanting it to select a specified unzipped file and then import that data to the Sql server.
Any thoughts?
Terry
December 10, 2009 at 6:49 am
Terry
SSIS can very easily do this. SSIS is an ETL Tool. You can eve make SSIS download the file from where ever and extract the 4 files from within. Some of the more in depth code is VB.NET, so you will be comfortable. SSIS is very powerful, but at the same time, some things are so very simple. open up BIDS and take a look. There are many books on SSIS that include samples that can be downloaded from the websites. I suggest that you download some and look them over to get an idea of all the things that SSIS can perform.
Andrew SQLDBA
December 10, 2009 at 6:54 am
The local administrator typically doesn't have rights on another machine. The path you show is a remote machine, and that's why it is suggested that it is suggested that SQL Server run under a domain (non admin) account.
The access denied is a windows error. you could get around this by having a copy in Windows to move the file to the local machine.
As far as importing, once it's local, you can bulk insert it.
However, if you are a programmer, as andrew suggested, SSIS can do all this for you. It can move the file and then import it (or not move it in and import it).
December 10, 2009 at 6:59 am
Andrew,
I will take a look at the Books. I do have one question though, the file names change very day based on the day. example: ABC_20091210.xml tomorrow, ABC_20091211.xml. How can SSIS handle this issue?
I've used and am using SSIS on a daily basis in some other vb.net programs, however none of the file names change, so in other words everything is static.
Terry
December 10, 2009 at 7:05 am
Steve,
The administrator account is the Domain Administrator account not the local machine account. Unfortunately moving the file from the remote pc to the SQL Server PC is not an option in this case.
As far as being a programmer, a newbie programmer. In the last month I've been assigned programming tasks in java, vb.net, delphi, and sql. So I'm dealing with the basic's in all of them.
Any further assistance would be welcomed.
Terry
December 10, 2009 at 7:18 am
Maybe it would be best if I pasted the code so you understand what I am talking about.
Sub M_AO()
Dim Z As String = Microsoft.VisualBasic.Format(Today.AddDays(-1), "MMddyyyy")
Dim AO_Name As New String("\\NETADM\M_IMPORT\AO-SUMM_M_" & (Z) & ".xml")
Console.WriteLine(AO_Name)
Dim AOSQL As String
Dim conn As New SqlConnection("Server=DEV09\DEVDB;Database=MTrans;Integrated Security=SSPI;")
Dim cmd As New SqlCommand
AOSQL = "DECLARE @xml XML" + Environment.NewLine() + _
"SELECT @xml = x.y " + _
"FROM OPENROWSET( BULK '" + AO_Name + "', SINGLE_BLOB ) x(y) " + Environment.NewLine() + _
"Insert into M_AO (ASSET_OWNER_NAME,SCHEDULED_DATE,SETTLEMENT_CODE, INVOICE, OPERATING_DATE, DA_ADMIN, DA_ASM_REG, DA_ASM_SPIN)" + Environment.NewLine() + _
"SELECT ASSET_OWNER_NAME,SCHEDULED_DATE,CAST(SETTLEMENT_CODE AS Float) AS SETTLEMENT_CODE, INVOICE, OPERATING_DATE, DA_ADMIN, DA_ASM_REG, DA_ASM_SPIN" + Environment.NewLine() + _
"FROM " + Environment.NewLine() + _
"(SELECT " + Environment.NewLine() + _
"summary.x.value('NAME[1]', 'VARCHAR(10)') AS ASSET_OWNER_NAME,summary.x.value('SCHEDULED_DATE[1]', 'VARCHAR(10)') AS SCHEDULED_DATE,line_items.x.value('CHG_TYP_ID[1]', 'VARCHAR(MAX)') AS CHG_TYP_ID,SUBSTRING(stlmt_typ.x.value('STLMT_TYP_CD[1]', 'VARCHAR(30)'), 2, 30 ) AS SETTLEMENT_CODE,stlmt_typ.x.value('(STLMT_TYP_CD/@INVOICE)[1]', 'VARCHAR(10)') AS INVOICE, stlmt_typ.x.value('OPERATING_DATE[1]', 'VARCHAR(10)') AS OPERATING_DATE,stlmt_typ.x.value('AMT[1]', 'FLOAT') AS AMT " + Environment.NewLine + _
"FROM @xml.nodes('SUMMARY') AS summary(x)" + Environment.NewLine() + _
"CROSS APPLY summary.x.nodes('LINE_ITEMS/CHG_TYP') line_items(x)" + Environment.NewLine() + _
"CROSS APPLY line_items.x.nodes('STLMT_TYP') stlmt_typ(x)) x" + Environment.NewLine() + _
"PIVOT ( MAX( AMT ) FOR CHG_TYP_ID In ( [DA_ADMIN], [DA_ASM_REG], [DA_ASM_SPIN]) ) pvt"
With cmd
.Connection = conn
.CommandType = CommandType.Text
.CommandText = (AOSQL)
End With
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub
Like I stated in my last post, how can I deal with the file name change in SSIS. Believe me If I can just use SSIS to handle this I will be happy. Just don't have a clue how too.
Any further assistance would be greatly welcomed.
Terry
December 10, 2009 at 8:13 am
Here are a couple SSIS links:
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
http://www.simple-talk.com/community/forums/thread/5173.aspx
http://www.sqlis.com/post/File-Watcher-Task.aspx
http://stackoverflow.com/questions/541693/how-can-i-specify-a-variable-file-name-using-ssis
Does the file name change according to a pattern (date, etc)? Can you calculate it? If so you can use scripts to calculate the name and change the import path to match the file.
As far as the admin thing, if you log into that PC as a domain admin, can you access the path?
December 10, 2009 at 8:31 am
"Does the file name change according to a pattern (date, etc)? Can you calculate it? If so you can use scripts to calculate the name and change the import path to match the file."
Yes, the file name changes according to a pattern, in the code I have the static part of the file name and then a variable (Z) that changes daily. the AO_name is then the Static part along with the Daily change variable (Z).
"As far as the admin thing, if you log into that PC as a domain admin, can you access the path? "
To answer this question the answer once again is "Yes". That is the reason why this isn't making any sense. Everything should be working and it isn't.
Terry
December 10, 2009 at 8:42 am
Terry
I am sure that using VB.NET, you have some code that can read the files in the directory and come back with all the file names. Am I correct? 🙂
You can use that in SSIS. All of the code behind the scenes can be written in VB.NET. I am a C# person, and I have to use VB.NET in SSIS. A little bit of change, but not too much. You can also put the file name together within the SSIS package and then perform a compare to see if it exists. I would suggest that you read the contents of the directory, that way you will always get the correct file. I would keep track of the file names, say store the name in a table, along with the current date to have something to compare. That way you can compare each day to verify that a new file is there, and that the current file has not already been pumped into the database. A little bit of verification can go a long way. If that file has been pumped in, fall out of the package after inserting a record into the audit table, and you could also email a group of people for notification that you have not received a new file for the day.
SSIS can do pretty much anything that you need. I even use it to move files from one location to another, delete files and create a new directory to store the files, etc..... That will give you some ideas.
Andrew SQLDBA
December 10, 2009 at 8:50 am
Andrew,
"I am sure that using VB.NET, you have some code that can read the files in the directory and come back with all the file names. Am I correct?" yes you would be correct with that statement. And I do bring in the filename and date into my table so i can differentate between dates, in fact it is one of my Primary Keys.
I guess my thing is trying to figure out how to run my code in the SSIS, from what you or Steve wrote I should be able to use SSIS to do the Bulk Insert into my remote database, that the SSIS would eliminate the windows network issue that I seem to be having.
Terry
December 10, 2009 at 9:54 am
SSIS just changes the way you could use accounts. You still need to get an account that works. Something doesn't seem right here. Are you executing this in SSMS and it doesn't work?
Also, please don't copy our posts to reply. It makes it hard to read. There's a quote button that will separate out things.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply