Access flat file on remote server from SSIS

Question

Access flat file on remote server from SSIS

lk-681111

SSCrazy

Points: 2915
More actions
June 15, 2010 at 10:33 am

#222862

Hello:
I've been scouring the Internet for instructions on how to do this, but maybe I'm not phrasing it correctly, because I can't seem to find anything that works.
I have an SSIS package that finds the most recently created log file in a share on a remote server, builds a connection string with the file name and assigns it to a variable, and then imports the data from the log file to a local instance of SQL Server. Here is the code I use in my script task:
Public Sub Main()
Dim rootDi As New DirectoryInfo("\\RemoteServerName\ShareName")
Dim newestFile As String
Dim fileDate As Date
Dim fileDateSaved As Date
For Each fi As FileInfo In rootDi.GetFiles("*.log")
fileDate = fi.CreationTime
If fileDate > fileDateSaved And fileDate < DateTime.Today Then
fileDateSaved = fileDate
newestFile = fi.Name
End If
Next
Dts.Variables("varFileName").Value = "\\RemoteServerName\ShareName\" + newestFile
Dts.TaskResult = Dts.Results.Success
End Sub
When our network admin assigned my domain account (under which the package was created) access permissions to the share on the remote server, the package ran fine, but the remote server is outside the firewall, so we don't want to use a domain account. The network admin created a username and password that I'm supposed to use to access the share from the SSIS package, but I can't figure out how to add this information to the package.
I hope I'm explaining this clearly enough--I'm not sure that I totally understand what I'm talking about. Please let me know if I need to clarify anything.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 1

This is something I just learned the other day in another post but haven't had a reason to use yet. You can use .NET Impersonation. It gives you a "Run As" capability in your code. If you Google that you will find several people have posted cut & paste code that you should be able to work into your SSIS package.

lk-681111 SSCrazy Points: 2915 More actions · Answer 2

Thanks for your suggestion, Ed! I actually happened upon a solution that seems to work for me, but I had never heard of the impersonation thing, so I'll definitely look into it further. Thanks! 🙂

In case anyone's interested, here's what worked for me:

I created a new job in SQL Agent and created two steps. In the first step, I selected "Type: Operating System (CmdExec)", "Run As: SQL Agent Service Account", and then I typed the following in the Command section:

Net Use \\RemoteServerName\ShareName SharePassword /User:MyDomain\ShareUserName /PERSISTENT:Yes

Then I made the second step run my SSIS package, and everything seems to have worked! Hurray!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Ed-86789 (6/16/2010)
This is something I just learned the other day in another post but haven't had a reason to use yet. You can use .NET Impersonation. It gives you a "Run As" capability in your code. If you Google that you will find several people have posted cut & paste code that you should be able to work into your SSIS package.

As I was reading his post I was thinking about that very suggestion..

CEWII

nhsingh123 Valued Member Points: 65 More actions · Answer 4

Hi,

I have the exact same scenario as yours. I need to access a flat file on a remote server which is generated every week, and want to store its data on a sql db on my local system. I see, you are using some code to make the connection. Can you elaborate where did you write this code? Is it in same BIDS where we have the packages?

How to execute it? Can you give me step by step details of this access?

Neha

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

nhsingh123 (7/30/2011)
Hi,
I have the exact same scenario as yours. I need to access a flat file on a remote server which is generated every week, and want to store its data on a sql db on my local system. I see, you are using some code to make the connection. Can you elaborate where did you write this code? Is it in same BIDS where we have the packages?
How to execute it? Can you give me step by step details of this access?
Neha

You'll need to use the Script Task.

http://msdn.microsoft.com/en-us/library/ms141752.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP