none
Good SSIS Example tutorial - User Variables RRS feed

  • Question

  • Hi There

    I have read BOL extensively and gone to SSIS tutorial/example web links all day.
    Problem is the examples are way to simple.

    And the tutorials on BOL especially the DW warehouse example have no explanantion or step by step flow, and the others are to simple.

    I would REALLY appreciate it if some had a link or resource to a good complicated step by step SSIS tutorial/example.
    Or more specifically not a basic one, i am most interested in using variables to control flow, and the use of variables in expressions to set properties of a task.

    For example if i set a variable equal to a single result set in a sql task, how do i use that variable later to control package flow, or in an expression to disable a task.

    In BOL and internet resources it basically just states you can use variable to do many things but no nice examples of how to do so.

    PLEASE help.

    Thanx
    Thursday, June 30, 2005 1:10 PM

Answers

  • Phew, this thread could go on for years Smile

    You say you want complicated examples. For what its worth, I would say that a complicated example is just an amalgamation of lots of simple examples. SSIS is such an easy tool to use that the only real complexity is using alot of tasks in a package. The individual tasks themselves are fairly simple and that's why its possible to build large, seemingly complicated, proof-of-concept solutions in a very short space of time.

    If you want to know how to use variables to control your package flow go here: http://www.sqlis.com/default.aspx?306

    If you want to use variables in expressions to set properties of a task (termed property expressions) then there's a nice example here of setting the SQLStatementSource property of an ExecuteSQL Task: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
    or here of setting various properties of the SendMail task: http://www.sqlis.com/default.aspx?59
    or here of setting the ConnectionString property of a FlatFile connection manager: http://blogs.conchango.com/jamiethomson/

    I haven't seen an example online of how to disable a task at runtime using an expression but believe me, it is very very easy, and there is enough material at the above links to show you how to do it. If you still have trouble then please reply here and I can put a simple demo together later.

    You may also want to look here which explains how expressions can also be used in a variable: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx

    Out of interest, under what circumstances are you needing to disable a task? I think that affecting your control-flow so that a task is never executed (as explained at the first link above) would be the better thing to do.

    In summary, everything we're talking about here is involving the use of expressions, i.e. workflow contraint expressions and property expressions. Its impossible to overstate just how powerful expressions can be in an SSIS solution - gotta be my favourite feature of SSIS I reckon.

    -Jamie
    Thursday, June 30, 2005 1:54 PM
    Moderator

All replies

  • Phew, this thread could go on for years Smile

    You say you want complicated examples. For what its worth, I would say that a complicated example is just an amalgamation of lots of simple examples. SSIS is such an easy tool to use that the only real complexity is using alot of tasks in a package. The individual tasks themselves are fairly simple and that's why its possible to build large, seemingly complicated, proof-of-concept solutions in a very short space of time.

    If you want to know how to use variables to control your package flow go here: http://www.sqlis.com/default.aspx?306

    If you want to use variables in expressions to set properties of a task (termed property expressions) then there's a nice example here of setting the SQLStatementSource property of an ExecuteSQL Task: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
    or here of setting various properties of the SendMail task: http://www.sqlis.com/default.aspx?59
    or here of setting the ConnectionString property of a FlatFile connection manager: http://blogs.conchango.com/jamiethomson/

    I haven't seen an example online of how to disable a task at runtime using an expression but believe me, it is very very easy, and there is enough material at the above links to show you how to do it. If you still have trouble then please reply here and I can put a simple demo together later.

    You may also want to look here which explains how expressions can also be used in a variable: http://blogs.conchango.com/jamiethomson/archive/2005/03/19/1163.aspx

    Out of interest, under what circumstances are you needing to disable a task? I think that affecting your control-flow so that a task is never executed (as explained at the first link above) would be the better thing to do.

    In summary, everything we're talking about here is involving the use of expressions, i.e. workflow contraint expressions and property expressions. Its impossible to overstate just how powerful expressions can be in an SSIS solution - gotta be my favourite feature of SSIS I reckon.

    -Jamie
    Thursday, June 30, 2005 1:54 PM
    Moderator
  • Hi Jamie

    Thank You so much for the input, i will check out all the links you provided.
    I just saw that it was an option to set the diabled property through an expression.
    For example i perform a sql task to see if there is any data in the data warehouse staging tables, if there is not ie: my variable is 0, i want to disable the delete data from staging tables sql tasks , but i agree changing the control flow is better.

    Thank you again i will reply again once i have checked out the links.

    Thank
    Thursday, June 30, 2005 2:01 PM
  •  SeanDL wrote:

    I just saw that it was an option to set the diabled property through an expression.


    Yeah, its definately an option. just not best practice!

    -Jamie
    Thursday, June 30, 2005 2:11 PM
    Moderator
  • Sean, I second Jamie's advocation of using property expressions here. Using them for enabling and disabling tasks is, I think, a bit of bad practice, especially for workflow control.
    At one time we considered special casing the enable/disable property on tasks so that property expressions couldn't be used to modify it. We backed away from that because there _are_ valid reasons to do this, but not for workflow.
    Thursday, June 30, 2005 9:47 PM
  • Hi Jamie

    The links are proving useful, thanx.
    If you have a minute to spare i have some queries about the send mail task example.
    I am not sure if you are fimiliar with it, but i get errors after configuring the send mail task, as no recipient is being supplied, but that is because ToLine is dynamically created by an expression, but SSIS will not run debug it is an error!? I get around this by assigning the variable a value not sure if this is right.

    Secondly when i run the example i get the following errors:

    Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ContactName" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 2 to variable "User::ContactName" cannot be applied.

    Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ClosureDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    Error: 0xC001C012 at Sending mails from a record set: ForEach Variable Mapping number 3 to variable "User::ClosureDate" cannot be applied.

    Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

    I dont get it , the variables are type string, the columns in the table are char, the only values being assigned to the variable are in the loop but i am not changing any types ?

    Thanx

    Friday, July 1, 2005 9:33 AM
  • Hi Jamie

    Dont worry i figured ou tthe problem, for some reason the result set output columns are not in the same order as specified in the select statement? Strange.But it works now, thanx for all your help.
    Friday, July 1, 2005 10:18 AM
  •  KirkHaselden wrote:
    We backed away from that because there _are_ valid reasons to do this, but not for workflow.


    And they are...?

    -Jamie
    Tuesday, July 5, 2005 2:23 PM
    Moderator
  • Valid reason?  Hmmmm, I'm not sure I have a proper list, it's more of a design principle. Ie., we take the approach that we don't know everything. Pretty safe assumption, wouldn't you say?
    We don't like to arbitrarily limit what folks can do just because we think it's bad practice. Someone may have a perfectly good reason for doing it that we haven't thought of.

    One I can think of is configuring "Disable" based on environment constraints, like configuring entire containers and enabling others. One could do this with a dummy task and precedence constraints, but the configuration method is more elegant in some cases like DBMaint.

    Tuesday, July 5, 2005 4:26 PM
  • Fair enough. I'd agree about it being bad practice to limit stuff. Sorry for making you think unnecessarily but when you said you had reasons I was interested to know what they were. :)
    Tuesday, July 5, 2005 5:10 PM
    Moderator
  • Sorry for making you think unnecessarily
    Yes, what a pain. :)
    K
    Tuesday, July 5, 2005 5:45 PM
  • Hi Sean,

    I am running into a similar problem. I have 1 input and 5 output variables to a stored procedure being called in a Execute SQL task, all defined as string and varchar. I am attempting to trap the return value (designated a long). I have everything defined as parameters.

    How do I figure out what order my variables are being passed and populated and if the order is not correct, how do I ensure the order?

    Thanks

    Arun

    Tuesday, March 21, 2006 12:53 AM
  • Arun,

    The parameters are mapped in the order that they appear in the Paremeter Mappings tab.

    -Jamie

     

    Tuesday, March 21, 2006 10:20 AM
    Moderator