Importing Data from CSV of a different server

Question

Post reply

Importing Data from CSV of a different server

ktflash

Default port

Points: 1400
More actions
March 15, 2022 at 11:00 am

#3999711
this is driving me nuts....
i am trying to get data from a csv file which is stored on a different server but for gods sake i can not manage to get this to be executed in a job
Here is what i have done so far:
- added the server/network in my "computer/This PC" by adding a new network location in the windows explorer
- created a flatfile connection manager browsing: Computer/This PC --> remote server which was added with the full path to the file
  - Executing this task to import Data from the file works, means i can access the file using the connection added network in my explorer
- Added Credentials and the proxy for SSIS Execution with the same user which sucessfully can run the package localy in VS
- Created a job which executes the SSIS Package which runs locally in VS, using the same user which can run the package in VS
Not sure if important, but i guess:
- Full Connection String: \\Server\folder\folder2\file.csv
- Shortend in Connection Manager: V:\file.csv
  - Not sure if it is just a visual thing, but whenever i connect (browsing/connection string) it displays the short directory
And this is where i can not get any further no matter what i tried and i dont know what else to try:
- Executing the Job with the package as is, results in the following error:
  - Cannot open Datafile, FlatFile Source failed to preexecute phase and retured error code 0xC020200E
  - I tried changing "DelayValidation" to true on connection manager aswell as the package level
- I tried adding the full file directory including servername and filename into the flatfile connection manager in 2 different ways:
  - First: into the browsing window of the connection manager
    It takes forever for VS to connect (VS Delay warning), the result is:
    "valid file name must be selected" warning when using the full connection string including file name (file name is correct)
    wenn i click on browse using the connection string with the server it finds the correct target folder on the other server ( after VS wait)
    so i get to the folder using the connection string, but when i chose the file, the directory is shortend again as stated above (V:\file.csv)
    On Execution: Cannot open Datafile, FlatFile Source failed to preexecute phase and retured error code 0xC020200E
  - Second: into the connection string expression of the con manager (with escaping the \)
    i checked the expression result of the expression which is correct
    ERROR: connection string validation failed, Filename (\\Server\folder\folder2\file.csv) specified in the connection was not valid
I have no idea how i am supposed to continue from here...
I canfirmed in the ssis exection that its the same user running the job.
I am not really suprised it is not working with the browsing feature through an added network, but i need to know how i can make it work to get the data from a connection string...
I found some Script Task Stuff but only walls of code of which most seem to do alot i dont actually need, but its hard for me the judge since ive never used C#

Attachments:
You must be logged in to view attached files.
I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Try using the full UNC path, eg:

\\servername\c$\folder\filename

(after verifying that the user running the job can access that path)

Also check that your proxy is being picked up properly by checking the All Executions report.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ktflash Default port Points: 1400 More actions · Answer 2

Phil Parkin wrote:

Try using the full UNC path, eg:
\\servername\c$\folder\filename
(after verifying that the user running the job can access that path)
.

i used tried the connection string including the full path of the file: \\tsclient\Z$\P90\spool_2_mp\ZRW\ZRW_FW.csv

the connection manager does not recognize the file, unless i click the browse button and then im in the folder i want to be

once i change datatype form txt to csv and select the file the string is changed back to this

\\tsclient\Z\P90\spool_2_mp\ZRW\ZRW_FW.csv

i tried using the connection string in the manager and in the connection string expression

for both manual execution in VS works like before, but trying to execute package via job results in the same error as before, i attached in screenshot

Phil Parkin wrote:

Also check that your proxy is being picked up properly by checking the All Executions report.

All Executions tab for "Caller" is the same as the user which can run the packet in VS.

Not sure if this was the right approach but when creating credentials for the propxy i just set the same pass the login uses for the login in SQL Server.

Attachments:

You must be logged in to view attached files.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Unless Z is a local drive on the server, those are not UNC paths. You should avoid mapped drives when doing this.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ktflash Default port Points: 1400 More actions · Answer 4

Phil Parkin wrote:

Unless Z is a local drive on the server, those are not UNC paths. You should avoid mapped drives when doing this.

I went to our infrastructure asking for the complete path

After getting the wrong path from them i was mad even more

I solved the issue the following way:

as stated earlier i was able to connect using the original connection string given to me with the windows explorer
when browsing the directory there i right clicked a folder in the browser and checked its properties
there in the properties i found the actual path i needed....

Now i feel stupid, but the good feeling of finding the solution is > the shame currently

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Well done on solving it. There's no shame in this, it was a learning experience 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

I agree with Phil. You can kick your own butt if you want (heh... I've got a sore butt from doing the same) but you learned something different and then shared it with us! You learned, we learned, and, not that it matters, you "earned some stripes" in the area of "absolute honesty". Well done!

--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)