SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Question Help


SSIS Question Help

Author
Message
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62314 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 Visits: 1417
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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62314 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
rocky_498
rocky_498
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 Visits: 1417
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
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62314 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214476 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62314 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214476 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62314 Visits: 13298
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
lshanahan
lshanahan
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1511 Visits: 438
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