Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Question Help


SSIS Question Help

Author
Message
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
rocky_498
rocky_498
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 1291
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,
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45018 Visits: 39887
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
lshanahan
lshanahan
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 436
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search