SSIS Question Help

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Well, you were asking How... You did not ask Why this is better.... 🙂

    Regards,Yelena Varsha

  • 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

  • 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?

  • 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

  • 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.

  • 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

  • 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,

  • 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