August 22, 2013 at 10:13 am
Hi Guys, I have question.
I am creating a SSIS Package, Its runs fine with no problem, Its kinda simple package, Let me explain...
Pulling the data from SQL Table and my destination is flat file (First flat file).
Here i want one more logic
I want to create one more flat file with below information
1) Count from First flat file
2) Flat file name (Naming convention change "Flatfilename_getdate()" from first flat file
3) Time when Package run..
Is anyone can help me
August 22, 2013 at 11:27 am
Hi,
You can use package variables.
Use @@Rowcount to get the number of rows returned by the query
http://technet.microsoft.com/en-us/library/ms187316.aspx
Assign this @@Rowcount value to a package variable
Generate the file name with GetDate() and assign it to both a package variable and to the file name
You don't need the time the package run as it is in the file name. Or get the time with GetDate() then assign it to both the file name and a package variable.
Then create a second file and write all 3 variables to it.
Yelena
Regards,Yelena Varsha
August 22, 2013 at 1:26 pm
Yelena Varshal (8/22/2013)
You can use package variables.
Use @@Rowcount to get the number of rows returned by the query
http://technet.microsoft.com/en-us/library/ms187316.aspx
Assign this @@Rowcount value to a package variable
Very, very curious how you would do this in an OLE DB Source component.
The way to go is to use the Rowcount component. It is there for a reason.
Yelena Varshal (8/22/2013)
Generate the file name with GetDate() and assign it to both a package variable and to the file name
More info on this:
Dynamic Flat File Connections in SQL Server Integration Services[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2013 at 2:33 pm
Hi,
I can for example use Execute SQL task that queries a Stored Procedure or function that returns @@Rowcount and assign the result to a package variable as decribed in
http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/
How To Set and Use Variables in SSIS Execute SQL Task
Yes, you can use the component too
Regards,Yelena Varsha
August 22, 2013 at 2:43 pm
Yelena Varshal (8/22/2013)
Hi,I can for example use Execute SQL task that queries a Stored Procedure or function that returns @@Rowcount and assign the result to a package variable as decribed in
http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/
How To Set and Use Variables in SSIS Execute SQL Task
How would you get the result of your stored procedure into the flat file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2013 at 3:08 pm
The result could be written to a variable per the link in my previous post. Then you can output the variable to a text file with the streamwriter using dts.variables collection
Or you can do it much easier. Creaate your own CLR stored procedure that will write stuff to a file and use it without SSIS as a single SQL Script for both result set output and for the second file.
You can also use bcp out
Regards,Yelena Varsha
August 22, 2013 at 3:28 pm
Yelena Varshal (8/22/2013)
The result could be written to a variable per the link in my previous post. Then you can output the variable to a text file with the streamwriter using dts.variables collectionOr you can do it much easier. Creaate your own CLR stored procedure that will write stuff to a file and use it without SSIS as a single SQL Script for both result set output and for the second file.
You can also use bcp out
That's a whole lot of trouble, while you can just use the OLE DB Source and a Rowcount.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 22, 2013 at 3:30 pm
Well, you were asking How... You did not ask Why this is better.... 🙂
Regards,Yelena Varsha
August 23, 2013 at 12:56 am
Yelena Varshal (8/22/2013)
Well, you were asking How... You did not ask Why this is better.... 🙂
True true 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 23, 2013 at 10:19 am
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?
August 25, 2013 at 1:08 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 25, 2013 at 4:44 pm
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.
August 26, 2013 at 1:30 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 8:12 am
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,
August 26, 2013 at 1:13 pm
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply