SSIS Storing File Name as a Variable

1

I am very new to SSIS. Like, less than a month of exp using it new.

Here is what I want to do:

In the "File name:" section of the File File Connection Manager editor, I want to replace this with a variable. i.e. variable\name_of_file.txt.

In the variables section, I have my path for the value. I expect this to change depending on who is using the ssis package.

The variable value will be "c:\users\john_doe\downloads\"

How do I get this variable into the fiat file connection manager editor step in the file name location?

In other words, my variable name is "file_output_path", so in the flat file connection manager, would I just set the file name to @file_output_pathtest.txt ?

Edit #1:

I edited the expression value in the file connection, but I'm running into some problems.

Here is how I have everything set up:

variable_list flat_file_connection_manager_setup setup Here is the error message that I am getting:

SSIS package

"C:\Users\xxx\Downloads\creating_validation_output\orphaned_plan_services_null_provider\orphaned_plan_services_null_provider\orphaned_plan_services_null_provider.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x402090DC at Data Flow Task, Flat File Destination [14]: The processing of file "C:\Users\xxx\Downloads\creating_validation_output\orphaned_plan_services_null_provider\test.txt\test.txt\test.txt" has started. Warning: 0x80070003 at Data Flow Task, Flat File Destination [14]: The system cannot find the path specified. Error: 0xC020200E at Data Flow Task, Flat File Destination [14]: Cannot open the datafile "C:\Users\xxx\Downloads\creating_validation_output\orphaned_plan_services_null_provider\test.txt\test.txt\test.txt". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E. Information: 0x402090DD at Data Flow Task, Flat File Destination [14]: The processing of file "C:\Users\xxx\Downloads\creating_validation_output\orphaned_plan_services_null_provider\test.txt\test.txt\test.txt" has ended. Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "Flat File Destination" wrote 0 rows. Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task Warning: 0x80019002 at orphaned_plan_services_null_provider: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "C:\Users\xxx\Downloads\creating_validation_output\orphaned_plan_services_null_provider\orphaned_plan_services_null_provider\orphaned_plan_services_null_provider.dtsx" finished: Failure. The program '[9320] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

I don't understand where the

\test.txt\test.txt\test.txt

is coming from.

ssis
ssis-2016
asked on Stack Overflow Oct 9, 2018 by LunchBox • edited Oct 9, 2018 by LunchBox

2 Answers

2

Almost all SSIS objects have a Properties window.

Select the Connection Manager, go to its Properties window, find the Expressions entry, and look for the ConnectionString entity. You can concatenate variables there to create your path at run-time.

answered on Stack Overflow Oct 9, 2018 by Brian
1

I figured out how to solve my problem.

If I tried to put the path into 1 variable and the file name in an expression that used the path variable + appended the file name at the end, it would add the weird ending.

If I put path and the file name into two separate variables, I did not have any problems with this.

answered on Stack Overflow Oct 9, 2018 by LunchBox

User contributions licensed under CC BY-SA 3.0