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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply