Post reply

Importing Data from CSV of a different server

  • 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

  • March 15, 2022 at 11:12 am

    #3999715

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 15, 2022 at 1:31 pm

    #3999814

    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

  • March 15, 2022 at 1:40 pm

    #3999846

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 18, 2022 at 7:27 am

    #4001784

    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

  • March 18, 2022 at 8:30 am

    #4001805

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 18, 2022 at 10:38 pm

    #4002130

    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)

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

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