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 Thursday, August 22, 2013 10:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 208, Visits: 763
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
Post #1487389
Posted Thursday, August 22, 2013 11:27 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
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 Varshal

Post #1487426
Posted Thursday, August 22, 2013 1:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 13,570, Visits: 10,447
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




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 #1487482
Posted Thursday, August 22, 2013 2:33 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
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 Varshal

Post #1487520
Posted Thursday, August 22, 2013 2:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 13,570, Visits: 10,447
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?




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 #1487525
Posted Thursday, August 22, 2013 3:08 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
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 Varshal

Post #1487539
Posted Thursday, August 22, 2013 3:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 13,570, Visits: 10,447
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 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


That's a whole lot of trouble, while you can just use the OLE DB Source and a Rowcount.




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 #1487545
Posted Thursday, August 22, 2013 3:30 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:35 PM
Points: 3,475, Visits: 580
Well, you were asking How... You did not ask Why this is better....


Regards,
Yelena Varshal

Post #1487547
Posted Friday, August 23, 2013 12:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 13,570, Visits: 10,447
Yelena Varshal (8/22/2013)
Well, you were asking How... You did not ask Why this is better....


True true




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 #1487652
Posted Friday, August 23, 2013 10:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 208, Visits: 763
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?
Post #1487918
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse