SSIS Storing File Name as a Variable

Question

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:

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.

_change depending on who is using the ssis package._ SSIS is definitely not an end user tool. — Nick.Mc, Oct 10 '18 at 00:19
The people using it are on the same team as me, they know how to use SSIS — Chicken Sandwich No Pickles, Oct 10 '18 at 13:14

score 2 · Accepted Answer · answered Oct 09 '18 at 20:40

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 Oct 09 '18 at 20:40

Brian

1,238
2
11
17

I am having some problems with this. I will edit my original post in just a second. – Chicken Sandwich No Pickles Oct 09 '18 at 22:47
1

I figured out my problem, but since you solved my original problem, I'll mark yours as the answer. – Chicken Sandwich No Pickles Oct 09 '18 at 23:52
I'm glad you got it squared away. That's a strange behavior you encountered when you implemented my initial suggestion - I've never seen SSIS act like that. – Brian Oct 10 '18 at 18:51
1

sorry - though I had. – Brian Oct 10 '18 at 19:58

score 1 · Answer 2 · answered Oct 09 '18 at 23:48

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.

SSIS Storing File Name as a Variable

2 Answers2