Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS Question Help Expand / Collapse
Author
Message
Posted Sunday, August 25, 2013 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
rocky_498 (8/23/2013)
Hi Guys,

Thank you all for your help. I used RowCount Tranformation...

One more question, I want to display Count and file name and put in to flat file. I got the Count is anyone can help me how i can get file name that I just rename through "File System Task" to add Date Stamp? I can get file name however date stamp is not matched from file that just renamed in File System Task.

Any help?


How did you calculate the file name in the file system task? If you used an expression with getdate(), it will change every time it is called.
The best option to have one constant file name is to calculate in an Execute SQL Task and store the result in a variable. This variable you would use in the file system task and to output it to the flat file destination.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488224
Posted Sunday, August 25, 2013 4:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 216, Visits: 788
In FileSystem Task I am Renaming file name, FileName_GETDATE(), so you are right file name gonna change. Here is the thing and I am not sure why its happening. When I am renaming file name I am saving the value in variable and variable value is =
e.g (FileName_20130825033922), Now what I need a same file name that I just rename it in File System task (e.g (FileName_20130825033922) but some how If i use the same variable to show the value I am getting the same value the value I am getting (e.g (FileName_20130825033924). Any help would be great appreciate.

Thank You.
Post #1488263
Posted Monday, August 26, 2013 1:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
The variable is being re-evaluated every time. That's why you need to calculate it once (in a script task or execute sql task) and store the result in a variable, instead of using an expression on a variable.

Or calculate the filename to the date level, it will most likely stay the same.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488306
Posted Monday, August 26, 2013 8:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:30 PM
Points: 216, Visits: 788
Thanks, that was I was thinking....

I am thinking how about If I get Recent file name from folder?

Is anyone can please guide me how i can recent file name from folder in SSIS?

Thank You,

Post #1488395
Posted Monday, August 26, 2013 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
rocky_498 (8/26/2013)
Thanks, that was I was thinking....

I am thinking how about If I get Recent file name from folder?

Is anyone can please guide me how i can recent file name from folder in SSIS?

Thank You,



You want the most recent file from the folder?
You'll probably want to do this with a script task, using some .net code.

http://stackoverflow.com/questions/1179970/c-sharp-find-most-recent-file-in-dir




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488483
Posted Tuesday, August 27, 2013 1:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
Koen Verbeeck (8/26/2013)
rocky_498 (8/26/2013)
Thanks, that was I was thinking....

I am thinking how about If I get Recent file name from folder?

Is anyone can please guide me how i can recent file name from folder in SSIS?

Thank You,



You want the most recent file from the folder?
You'll probably want to do this with a script task, using some .net code.

http://stackoverflow.com/questions/1179970/c-sharp-find-most-recent-file-in-dir


Heh... isn't that nice... someone took the time to create the DOS DIR command with a /o-d switch in C#. I don't know much about SSIS but why can't you use something like a CmdExec task and take the top 1 from that return? Better yet, isn't there some way to get and loop through a list of files in a certain order. Couldn't you setup a single iteration loop for such a thing?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488579
Posted Tuesday, August 27, 2013 1:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Jeff Moden (8/27/2013)
Koen Verbeeck (8/26/2013)
rocky_498 (8/26/2013)
Thanks, that was I was thinking....

I am thinking how about If I get Recent file name from folder?

Is anyone can please guide me how i can recent file name from folder in SSIS?

Thank You,



You want the most recent file from the folder?
You'll probably want to do this with a script task, using some .net code.

http://stackoverflow.com/questions/1179970/c-sharp-find-most-recent-file-in-dir


Heh... isn't that nice... someone took the time to create the DOS DIR command with a /o-d switch in C#. I don't know much about SSIS but why can't you use something like a CmdExec task and take the top 1 from that return?


That's possible as well
(I was around 5 years old when DOS was common use, so I'm not really up to speed with it's language )




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488581
Posted Tuesday, August 27, 2013 1:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
Glad some things never change.

I edited my post without realizing that you had responded. Isn't there some "For Each" loop that can get and loop through files in a certain order and just limit the number of iterations to 1?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488582
Posted Tuesday, August 27, 2013 1:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
Jeff Moden (8/27/2013)
Glad some things never change.

I edited my post without realizing that you had responded. Isn't there some "For Each" loop that can get and loop through files in a certain order and just limit the number of iterations to 1?


Yes, there's a FOR loop, but I don't think you can specify a fixed order. (hence my suggestion for .NET where you have more control)
Hey, I never said SSIS was perfect




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488590
Posted Wednesday, August 28, 2013 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 15, 2014 6:44 AM
Points: 132, Visits: 246
Koen Verbeeck (8/27/2013)
Yes, there's a FOR loop, but I don't think you can specify a fixed order. (hence my suggestion for .NET where you have more control)
Hey, I never said SSIS was perfect


I've created several packages for SSIS which look for the most recent file in a directory, and I've never found a way other than using a .NET script, or at least not one that isn't as much if not more work.


____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Post #1489160
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse