September 15, 2011 at 4:58 pm
i have a flat files generated everyday night by format YYYYMMDD HHMMSS_01A.txt
my task is that i have to create a package that select todays dateTime file .
For that Am using some thing like this ..
"//jls-devprescnt\\xxxxx\\"+(DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" "+
RIGHT("0"+(DT_WSTR,2)DATEPART("hh",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("mi",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("ss",getdate()),2)+
"_01A.txt"
But the Problem is that it is selecting the time which am executing the package like
//jls-devprescnt\Inbound\20110914 091710_01A.txt
but the file is in //jls-devprescnt\Inbound\20110915 60336_01A.txt
I want package to select the file regardless of time but with of Date and file name i.e 20110915 _01A.txt
can anyone please explain me in detail how to do that
September 15, 2011 at 5:17 pm
You are specifying in your statement to look for a file based on Year,Month,Day and time
"//jls-devprescnt\\xxxxx\\"+(DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" "+
RIGHT("0"+(DT_WSTR,2)DATEPART("hh",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("mi",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("ss",getdate()),2)+
"_01A.txt"
The three commented lines are causing your issue. You need to remove those and substitute a wildcard instead.
"//jls-devprescnt\\xxxxx\\"+(DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" "+ % +
--RIGHT("0"+(DT_WSTR,2)DATEPART("hh",getdate()),2)+
--RIGHT("0"+(DT_WSTR,2)DATEPART("mi",getdate()),2)+
--RIGHT("0"+(DT_WSTR,2)DATEPART("ss",getdate()),2)+
"_01A.txt"
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 5:34 pm
Thank for the reply..
am getting an error
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
------------------------------
ADDITIONAL INFORMATION:
Attempt to parse the expression ""C:\\ssis_training\\" +(DT_WSTR,4)DATEPART("yy",getdate())+
RIGHT("0"+(DT_WSTR,2)DATEPART("MM",getdate()),2)+
RIGHT("0"+(DT_WSTR,2)DATEPART("dd",getdate()),2)+" "+ % +"_06B.txt"" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
(Microsoft.DataTransformationServices.Controls)
------------------------------
BUTTONS:
OK
------------------------------
September 15, 2011 at 5:37 pm
Curses the text editor. I deleted that %.
Anyway,
where and how is that expression being used? Is it within a data flow or as part of a proc?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 5:41 pm
actually am using it in expression of flat file connection manager to select file dynamically.
thanks
September 15, 2011 at 5:44 pm
K
One of two things has worked well for me. Either use a for each file enumerator and bring in all of the file names - then pick the file matching the name most closely.
The other is to use a script task to pull in the file from the file system and avoid the expression altogether.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 5:46 pm
Can you Please explain those two.
Thank You
September 15, 2011 at 5:48 pm
Here is a tutorial on one such solution.
http://microsoft-ssis.blogspot.com/2011/01/use-filedates-in-ssis.html
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 5:50 pm
Can you please tell me about the file enumerator??
Thanks
September 15, 2011 at 5:57 pm
Try this
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy