Flat File Connection Manager issue

Question

Post reply

Flat File Connection Manager issue

as_1234

SSCrazy

Points: 2865

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

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

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

palandri Old Hand Points: 387 More actions · Answer 1

This may help

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

Phil Parkin SSC Guru Points: 246974 More actions · Answer 2

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.

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

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 3

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)

as_1234 SSCrazy Points: 2865 More actions · Answer 4

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

TangoVictor SSChampion Points: 10975 More actions · Answer 5

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, 10 months ago by TangoVictor.

as_1234 SSCrazy Points: 2865 More actions · Answer 6

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?

Phil Parkin SSC Guru Points: 246974 More actions · Answer 7

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

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

as_1234 SSCrazy Points: 2865 More actions · Answer 8

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, 10 months ago by as_1234. Reason: Editted to include more of the error message

TangoVictor SSChampion Points: 10975 More actions · Answer 9

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

as_1234 SSCrazy Points: 2865 More actions · Answer 10

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