none
How to create a dynamic Flat File Source without specify a path?

    Question

  • Hi all,

    I want to use a expression, is it possible to create a Flat File Source without clcking "Broswe" and then select a hard coded path? It looks like I need to give it a hard coded path before i can assign a  dynamic expression to it?

    I know you may ask why I have to do this, why you can't just put a hard coded path and then assign the dynamic expression? It should override the hard coded path anyway.

    That is because I have a strange problem here: If i do hard coded path first then assign the dynamic expression (Btw, the expression is a variable created by a script task to generate a dynamic network file path: \\servername\foldername\filename). I can debug the package perfectly fine from Visual Studio and data gets inserted into my SQL database based on the dynamic file path. However, when I put it into a SQL agent scheduled task, each time when it runs it is still pointing to the hard coded path and the job fails.

    I checked the "Data sources" tab under the "Steps" tab of scheduled job properties, the connection string of the "SourceConnectionFlatFile" is still the hard coded path (please see below screenshot). Shouldn't it be the dynamic file path?

    I couldn't think of any other reason causing this issue, my best guess is if I can create a dynamic Flat File Source without specify a hard coded path, the expression might work.

    Btw, I didn't use a For Each loop container on the script, could that be the problem?

    Can anyone shine some lights here? This issue drives me crazy........

    Thank you in advanced.


    • Edited by ironwires Thursday, March 16, 2017 11:33 AM
    Thursday, March 16, 2017 11:29 AM

Answers

  • First of all, I dont think you have to do a for each loop or container.  The process will be the same, that just makes the variable be dynamic.

    When you open your variable window pane, does your variable have a value in it?  Is that the right value? 

    Is there something that sets that value dynamically, such as a step before it, or a package configuration (see below for more info on that)?

    Package Config:

    * Right click on the tan-ish area of the Control Flow tab and choose Package Configurations.  This sections allows you to have a dynamic value set at the start of the package.  We use these a lot for connection strings, etc., because you can change the xml file (or other method) easily without making any changes to the package.  If your variable has a package config which sets it, you will need to check the file name and path (possibly on the server where it is running if the C:\ drive is used, or share, etc) to see what the value is.


    - Glenda


    • Proposed as answer by Nr5952 Friday, March 17, 2017 5:48 PM
    • Edited by ggable313 Friday, March 17, 2017 7:11 PM
    • Unproposed as answer by ironwires Monday, March 20, 2017 5:52 AM
    • Marked as answer by ironwires Tuesday, March 21, 2017 4:52 AM
    Friday, March 17, 2017 5:39 PM
  • Thank you all for answering my question, appreciate that.

    I finally figured it out myself, the SSIS and SQL schedule task settings are all fine, the problem is the permission: The account for running the SQL job agent is local admin account of the SQL server, which doesn't has the permission to read file from the remote location where the flat file located. I changed the account from local admin to a domain account which has full permission and all working fine now.

    I don't think the SQL job agent event logs is helping here, the contents of the log doesn't identify the permission issue, instead of leading me to wrong ways, which caused me few days try to fix it.


    • Marked as answer by ironwires Tuesday, March 21, 2017 4:52 AM
    Tuesday, March 21, 2017 4:51 AM

All replies

  • Hi ironwires,The above does not accept expressions.

    The proper place for specifying the connection dynamically is the Flat File Connection Manager at the package design phase.


    Arthur

    MyBlog


    Twitter

    Thursday, March 16, 2017 1:06 PM
    Moderator
  • Hi Arthur,

    Thank you for your reply.

    What you are saying is below image of the Flat File Connection property , right?

    If so, I did set it up... The variable TF08 should override the hard coded path which it works  perfectly when I debug the package in BIDS. But when I put this package into a SQL schedule task in SQL job agent, the schedule task still take the hard coded path as in my previous post, then the job fails.  Any idea?  Thank you.

    Thursday, March 16, 2017 8:40 PM
  • 1) you may want to change DelayValidation = true ... that way it wont try to validate things until actual run time (not before)

    2) Is your variable being changed or not existed because it is also being set by something else?  If there is no value in the variable, then it will revert back to the hard coded value.  Either a step before this, or a configuration file could change the value to be blank for the variable.

    3) you might be able to set up your file connection manager, then delete the value after before you deploy it.  That way there is no hard coded value.  I haven't tried to deploy it, but it has let me delete it and it saves it as blank.

     

    - Glenda


    • Edited by ggable313 Thursday, March 16, 2017 8:53 PM
    • Proposed as answer by RyanAB Thursday, March 16, 2017 9:40 PM
    Thursday, March 16, 2017 8:52 PM
  • 1) you may want to change DelayValidation = true ... that way it wont try to validate things until actual run time (not before)

    2) Is your variable being changed or not existed because it is also being set by something else?  If there is no value in the variable, then it will revert back to the hard coded value.  Either a step before this, or a configuration file could change the value to be blank for the variable.

    3) you might be able to set up your file connection manager, then delete the value after before you deploy it.  That way there is no hard coded value.  I haven't tried to deploy it, but it has let me delete it and it saves it as blank.

     

    - Glenda


    Step 1 here is the key, you need to delay validation so that it doesn't attempt to access the file until it actually needs it
    Thursday, March 16, 2017 9:40 PM
  • Thank you for your reply, Glenda.<o:p></o:p>

    1) Have tried that, but it doesn't work:(<o:p></o:p>

    2) I am not sure about this, but I can run/debug it perfectly fine in BIDS, so the only possibility is the variable was changed by something in the SQL scheduled task side? Or maybe it is because I didn't use a for/for each loop container? <o:p></o:p>

    3) What I did is I setup the file connection manager with a hard coded value first, then setup the dynamic connection string/expression under Flat File Connection property, then run/debug it. After that, when I check the "file name" of the Flat File Connection, it becomes empty.  Is that the correct way of doing it?

    -------------------------------------------------------------------------------------------------------------------------

    Updated:

    I created a new SSIS package and put the exact the same configuration into it, except this time I replaced the hard coded local path with a hard coded network path. 

    After run it under a scheduled task, the error log becomes:

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
    03/17/2017 14:42:00,ALS,Error,0,XXXXXXXX,ALS,(Job outcome),,The job failed.  The Job was invoked by Schedule 11 (ALS_Daily).  The last step to run was step 1 (ALS).,00:00:01,0,0,,,,0
    03/17/2017 14:42:00,ALS,Error,1,XXXXXXX,ALS,ALS,,Executed as user: XXXXXXX\Administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6000.34 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:42:00 PM  Error: 2017-03-17 14:42:01.35     Code: 0xC0202070     Source: AKL08 Connection manager "ALS08"     Description: The file name property is not valid. The file name is a device or contains invalid characters.  End Error  Error: 2017-03-17 14:42:01.46     Code: 0xC0202070     Source: AKL08 Connection manager "ALS08"     Description: The file name property is not valid. The file name is a device or contains invalid characters.  End Error  Error: 2017-03-17 14:42:01.54     Code: 0xC0202070     Source: AKL08 Connection manager "ALS08"     Description: The file name property is not valid. The file name is a device or contains invalid characters.  End Error  Error: 2017-03-17 14:42:01.54     Code: 0xC020207E     Source: Data Flow Component Source for ALS08 [1]     Description: The file name is not valid. The file name is a device or contains invalid characters.  End Error  Error: 2017-03-17 14:42:01.54     Code: 0xC004701A     Source: Data Flow Component SSIS.Pipeline     Description: component "Source for ALS08" (1) failed the pre-execute phase and returned error code 0xC020207E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:42:00 PM  Finished: 2:42:01 PM  Elapsed:  0.639 seconds.  The package execution failed.  The step failed.,00:00:01,0,0,,,,0

    When I checked the "Data sources" tab under the "Steps" tab of scheduled job properties, the connection string of the "SourceConnectionFlatFile" becomes EMPTY:

    This make me certain that the dynamic connection string was not passed from SSIS to SQL schedule task for some reason.

    Any idea? Do I have to use a for/for each loop container or something like that?

    Thank you.

    Friday, March 17, 2017 4:48 AM
  • First of all, I dont think you have to do a for each loop or container.  The process will be the same, that just makes the variable be dynamic.

    When you open your variable window pane, does your variable have a value in it?  Is that the right value? 

    Is there something that sets that value dynamically, such as a step before it, or a package configuration (see below for more info on that)?

    Package Config:

    * Right click on the tan-ish area of the Control Flow tab and choose Package Configurations.  This sections allows you to have a dynamic value set at the start of the package.  We use these a lot for connection strings, etc., because you can change the xml file (or other method) easily without making any changes to the package.  If your variable has a package config which sets it, you will need to check the file name and path (possibly on the server where it is running if the C:\ drive is used, or share, etc) to see what the value is.


    - Glenda


    • Proposed as answer by Nr5952 Friday, March 17, 2017 5:48 PM
    • Edited by ggable313 Friday, March 17, 2017 7:11 PM
    • Unproposed as answer by ironwires Monday, March 20, 2017 5:52 AM
    • Marked as answer by ironwires Tuesday, March 21, 2017 4:52 AM
    Friday, March 17, 2017 5:39 PM
  • Step 1 does work, it's just you have something else set incorrectly so that by the time the file is accessed, it is not pointing to a file that exists.

    When you assign the connection string expression, did you click "Evaluate"? What did the output show? I assume an empty box which will become your default connection string - as long as you have set Delay Validation, you will be fine in that regard.

    When it comes to running, you need to ensure that by the time the file is to be accessed, you have set it to a valid path/file so where are you setting your variable(s) used to make up your connection string, check they're configured correctly (set some breakpoints and validate their values)

    Monday, March 20, 2017 7:35 PM
  • Thank you all for answering my question, appreciate that.

    I finally figured it out myself, the SSIS and SQL schedule task settings are all fine, the problem is the permission: The account for running the SQL job agent is local admin account of the SQL server, which doesn't has the permission to read file from the remote location where the flat file located. I changed the account from local admin to a domain account which has full permission and all working fine now.

    I don't think the SQL job agent event logs is helping here, the contents of the log doesn't identify the permission issue, instead of leading me to wrong ways, which caused me few days try to fix it.


    • Marked as answer by ironwires Tuesday, March 21, 2017 4:52 AM
    Tuesday, March 21, 2017 4:51 AM