July 5, 2012 at 3:26 pm
Hi, my requirement is to load a text file into SQL Server using SSIS.
The file name will be CCYYMMDD_HHMMSS_update.txt
I know that I can not ask SSIS to match up the HHMMSS because the file is generated each day but I must at least have it check the the CCYYMMDD stamp is today before processing the file.
Like this: CCYYMMDD*update.txt
Can I define a connection manager to a connection string with a wildcard? I'm assuming not so I have created a ForEach Loop but I don't know how to get the variable file name into the Files: box in the loop editor.
I tried making a variable called FileDate and setting it to
Right("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)
to generate 20120705 then setting Files within the ForEach Loop to the below but it is not finding a matching file.
[@FileDate]*update.txt
Is there a solution that will work for this?
July 5, 2012 at 3:37 pm
PHXHoward (7/5/2012)
Hi, my requirement is to load a text file into SQL Server using SSIS.The file name will be CCYYMMDD_HHMMSS_update.txt
I know that I can not ask SSIS to match up the HHMMSS because the file is generated each day but I must at least have it check the the CCYYMMDD stamp is today before processing the file.
Like this: CCYYMMDD*update.txt
Can I define a connection manager to a connection string with a wildcard? I'm assuming not so I have created a ForEach Loop but I don't know how to get the variable file name into the Files: box in the loop editor.
I tried making a variable called FileDate and setting it to
Right("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2)
to generate 20120705 then setting Files within the ForEach Loop to the below but it is not finding a matching file.
[@FileDate]*update.txt
Is there a solution that will work for this?
I don't have 2k8 up, so bear with me I'm running from 2k5.
What is CCYY standing for? Century Century Year Year? I'm used to seeing that as simply YYYY.
The expression setting you want is FileNameRetrieval. You set that on the first page under 'expressions', not outside the component.
As a final check, go into the expression component and confirm the evaluate expression piece is giving you what you expect.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 3:58 pm
Thanks Craig.
Yes, CCYY is a way of indicating 4 digit year.
The file is called
20120705_112233_UPD.TXT
and I have
Right("0" + (DT_STR,4,1252) DatePart("yyyy",getdate()),4)+
Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + "*UPD.TXT"
in the expression for FileNameRetrieval but it still comes back with "didn't find any files that match the patern".
Because I have the directory specificed in the line above, I don't need it in the file name do I? I've tried fully qualified and name only.
July 5, 2012 at 4:38 pm
PHXHoward (7/5/2012)
Because I have the directory specificed in the line above, I don't need it in the file name do I? I've tried fully qualified and name only.
Nope, you don't need to fully qualify the filename too.
Fully qualified and name only options there are actually for your output variable, not input.
Start with basics. Swap the expression to a hard-value (IN the expression, just type it in there) of the actual filename, and see if it'll detect then. If not, there's problems that don't have anything to do with the expression composition. If it locates it, then start including pieces into the 'hard name' one by one until you figure out which specific piece of it is broken.
Let's make sure we're troubleshooting the right piece, first. I'll see if I can't create a simple test structure on my side, everything I've got right now is production level code so it's not going to isolate the issue. Will take me a bit to be able to do that today.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 4:43 pm
Thanks Craig. I thought maybe it was not interpretingthe * in the middle of the file name as a wildcard through the variable. I'll keep trying to narrow it down.
July 5, 2012 at 4:58 pm
No problem. HAd a chance to setup a really simply test, and we've got the wrong expression. Trying to figure out which one it needs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 4:59 pm
It's FileSpec, not FileNameRetrieval. Sorry about that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 4:59 pm
Yes, I found if I type UPD.TXT into the File: box it works, if I do it using that expression, it does not find the file.
Thanks for helping me figure this out.
July 5, 2012 at 5:02 pm
It is working perfectly now with the * in the middle of the file name. Thanks so much for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply