Configuring Flat File Connection Manager at run time

[developer155]

Hi,
I am trying to set Connection property of Flat File Connection Manager at run time at the Expressions window by doing something like this:
@User::SomeVar + "\\textfile.txt"

I get an error at run time. Looks like SSIS evaluates expression correctly but cannto wrte into the file (although the file is in the folder).
I get an error "File \\blah\blah\blah\textfile.txt" not found. I checked and the file is there (empty), since I created it.
What is going on??

thanks!

 

[developer155]

Actually the exact error is this:

[Connection manager "NewsletterOptoutConnectionManager"] Error: The file name "\\clservices\D\EMNFtpRoot\P1-CSAV\PL24-Garden\Optout\optout.txt " specified in the connection was not valid.


I am not sure whats going on but I created the text file in that directory. What could be the reason it finds the file not valid?

 

[Catadmin]

Is the Flat File name something that changes all the time or something that is consistent?

What about the Path name? Does it change or is it consistent?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[developer155]

flat file name shoudl stay the same, but the directory shoudl change every time

 

[Catadmin]

The concept is the same, though. You populate your global package variable PathName (or whatever you want to call it) at run time, tag on the file name to it, and add it to the Expressions -> ConnectionString in the Connection Manager.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

YIKES! Ignore my previous post. I was thinking I was in a different thread. My apologies.

DOH!

Actually, though, I may have been on the correct track. I have another thread on this forum (thread1555-1265108) for creating dynamic file names, but the concept could be applied to this.

Create 3 global variables, PathName, FileName, PathAndFileName. Or call them something else if you want. Anyway, the key is how you determine your path name. Create a Script Task that reads in the FileName variable, use the Script Task to set the PathName and the PathAndFileName. Do your concatenation in the actual Script Task when setting the global variables. Set the Script Task as one of the precedences to your actual transformation task.

Then go to Expressions, choose ConnectionString and then drop and drag the User variable USER:athAndFileName down and click "evaluate expression" just to double-check and make sure it isn't doing anything funky.

This should make your connection work for you. If you don't know how to script, check out my thread for what scripts I used. I used MsgBox while debugging to verify what I was setting my globals variables to, then commented them out when I was done.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

You need to strip off the spaces in the file name.

I just gave someone a walk through on basically doing this here thread1555-1269514.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com