Post reply

Flat File Connection Manager issue

  • February 4, 2021 at 7:58 pm

    #3841140

    All,

    Firstly apologises as I think I've missed the obvious. I've been looking at this for a while and also tried searching but not found an answer.

    This is the XML for a Flat File Connection Manager:

        <DTS:ConnectionManager
          DTS:refId="Package.ConnectionManagers[Location]"
          DTS:CreationName="FLATFILE"
          DTS:DTSID="{1A92C503-A5B8-48B6-9509-07981365F5B6}"
          DTS:ObjectName="Location">
          <DTS:PropertyExpression
            DTS:Name="ConnectionString">@[$Package::FilePath] + "\\Location.csv"</DTS:PropertyExpression>
          <DTS:ObjectData>
            <DTS:ConnectionManager
              DTS:Format="Delimited"
              DTS:LocaleID="2057"
              DTS:HeaderRowDelimiter="_x000D__x000A_"
              DTS:ColumnNamesInFirstDataRow="True"
              DTS:RowDelimiter=""
              DTS:TextQualifier="_x003C_none_x003E_"
              DTS:CodePage="1252"
              DTS:ConnectionString="C:\Users\AS\source\repos\Airport\DataImport\Location.csv">
              <DTS:FlatFileColumns>
                <DTS:FlatFileColumn
                  DTS:ColumnType="Delimited"
                  DTS:ColumnDelimiter="_x002C_"
                  DTS:MaximumWidth="50"
                  DTS:DataType="129"
                  DTS:TextQualified="True"
                  DTS:ObjectName="Title"
                  DTS:DTSID="{B83E873D-28DE-4BD7-ABD8-D0D1E7A5CF0B}"
                  DTS:CreationName="" />
                <DTS:FlatFileColumn
                  DTS:ColumnType="Delimited"
                  DTS:ColumnDelimiter="_x002C_"
                  DTS:MaximumWidth="50"
                  DTS:DataType="129"
                  DTS:TextQualified="True"
                  DTS:ObjectName="PartOf"
                  DTS:DTSID="{9F75F9E0-C980-4C23-AAE7-D8D3FD3E9458}"
                  DTS:CreationName="" />
                <DTS:FlatFileColumn
                  DTS:ColumnType="Delimited"
                  DTS:ColumnDelimiter="_x002C_"
                  DTS:MaximumWidth="50"
                  DTS:DataType="129"
                  DTS:TextQualified="True"
                  DTS:ObjectName="MaxPassengers"
                  DTS:DTSID="{E5977CA6-EA52-472A-BC1B-F742094A2B7F}"
                  DTS:CreationName="" />
                <DTS:FlatFileColumn
                  DTS:ColumnType="Delimited"
                  DTS:ColumnDelimiter="_x002C_"
                  DTS:MaximumWidth="50"
                  DTS:DataType="129"
                  DTS:TextQualified="True"
                  DTS:ObjectName="LocationType"
                  DTS:DTSID="{894A3EFA-2007-4CF1-965A-9A3085D6E775}"
                  DTS:CreationName="" />
                <DTS:FlatFileColumn
                  DTS:ColumnType="Delimited"
                  DTS:ColumnDelimiter="_x000D__x000A_"
                  DTS:MaximumWidth="50"
                  DTS:DataType="129"
                  DTS:TextQualified="True"
                  DTS:ObjectName="MinTime"
                  DTS:DTSID="{1E64196B-FD99-44B5-8A58-A04EC2953895}"
                  DTS:CreationName="" />
              </DTS:FlatFileColumns>
            </DTS:ConnectionManager>
          </DTS:ObjectData>
        </DTS:ConnectionManager>

    When I try to run the project I get the error:

    Severity Code Description Project File Line Suppression State

    Error Error saving Package.dtsx: The connection "C:\Users\AS\source\repos\Airport\DataImport\Location.csv" is not found. This error is thrown by Connections collection when the specific connection element is not found. C:\Users\AS\source\repos\Airport\Airport_SSIS\Package.dtsx 1

    I think it's failing during validation rather than at runtime.

    However, whether it's during validation or runtime, it should be able to find the file. I can copy the path from the xml file (and from the error) paste it into 'start/run', click OK and it opens.

    I tried removing the ConnectionString specified in the expressions but that didn't help.

    I also tried 'ValidateExternalMedia' set to false and true

    It's running through Visual Studio so should be using my account? In which case as I can access the file through 'start/run' it's not a permissions issue?

    I would appreciate any ideas on what is causing the issue.

    Thanks

     

  • February 4, 2021 at 8:34 pm

    #3841146

     

    This may help

    https://stackoverflow.com/questions/11874722/ssis-connection-not-found-in-package

  • February 4, 2021 at 9:57 pm

    #3841164

    Sounds like the package file XML may be corrupt.

    Are you able to completely delete and recreate the connection?

    If not, it may be a good idea to start afresh with a new blank package and recreate the problematic package there.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • February 4, 2021 at 11:40 pm

    #3841201

    Does the path you listed ( "C:\Users\AS\source\repos\Airport\DataImport\Location.csv") live on your SSIS box or on a different machine?

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

  • February 5, 2021 at 2:32 pm

    #3841537

    All,

    Thank you for your help. The file is on the SSIS server.

    As it looks like I need to rebuild the package I will try that. I won't get time for a few days. I'll post an update when I've done that.

    Thanks

  • February 5, 2021 at 5:41 pm

    #3841598

    These problems are almost always security related.

    Your proxy account has access to this location? Or if running as the agent then your agent account has access here?

    Could always try running ProcMon on the server and see if you get any "Access Denied" errors for the dtexec.exe process.

    • This reply was modified 3 years, 5 months ago by  TangoVictor.

  • February 6, 2021 at 5:18 pm

    #3841935

    AlphaTangoWhiskey:

    Thanks for your help. I don't think the account being used for any proxy or the SQL Server agent will be used when I'm running the package from Visual Studio? It should use the account I'm logged onto Windows with?

  • February 6, 2021 at 8:22 pm

    #3841966

    as1981 wrote:

    AlphaTangoWhiskey:

    Thanks for your help. I don't think the account being used for any proxy or the SQL Server agent will be used when I'm running the package from Visual Studio? It should use the account I'm logged onto Windows with?

    Correct.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • February 8, 2021 at 1:30 pm

    #3842452

    All,

    Thanks for your help. I think I solved the issue by repairing Visual Studio. I thought it was my error rather than an issue with the software but perhaps not.

    Since the reinstall the project runs and the dataflow completes. A subsquent move file task gives the error "Failed to lock variable" ...error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created"

    The expression evaluates OK in design time.

    I'm still working on that. I just wanted to post an update in case anyone else gets the same original error I did.

    Software versions in case it helps:

    Visual Studio: 16.8.4

    Microsoft SQL Server Integration Services Designer 15.0.2000.152

    • This reply was modified 3 years, 4 months ago by  as1981. Reason: Editted to include more of the error message

  • February 8, 2021 at 4:44 pm

    #3842549

    yeah, sorry I misread the original, thought you were having an issue on the server.

  • February 9, 2021 at 11:32 am

    #3842867

    @AlphaTangoWhisley - No problem

    All:

    The 'failed to lock' runtime error on the file operation was my error. I had the expression for the filenames specified in the source and destination properties on the file system task. I have now changed the properties to specify connection managers and put the filename expressions on the connection managers and the package is now working.

    Thanks for your help.

Viewing 11 posts - 1 through 10 (of 10 total)

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