Good SSIS Example tutorial - User Variables

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.

ThanxPhew, this thread could go on for years

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

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

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

KirkHaselden wrote:

We backed away from that because there _are_ valid reasons to do this, but not for workflow.

And they are...?

-Jamie|||

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.

|||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. :)|||Sorry for making you think unnecessarily
Yes, what a pain. :)
K|||

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

|||

Arun,

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

-Jamie

Good SSIS Example tutorial - User Variables

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.

ThanxPhew, this thread could go on for years

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

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

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

KirkHaselden wrote:

We backed away from that because there _are_ valid reasons to do this, but not for workflow.

And they are...?

-Jamie|||

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.

|||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. :)|||Sorry for making you think unnecessarily
Yes, what a pain. :)
K|||

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

|||

Arun,

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

-Jamie

global variables urgent,

hey
i declare 3 global variables inside a formula.but can not refer those variables in another formula in the same report.
tell me how & where to declare global variables.
thanx.hi
try using this way :

formula @.x
numbervar a;
a:=10+20;

formula @.y
numbervar b;
numbervar a;
b:= 20+30;
b:=a+b;

as many variable as you want decalre in a formula, but the each formula should declare the variable.

This will give an idea about the global varialbles.
please try and let me know.|||Use shared variable also. Read it more in help file

global variables in dtspump task

I have two connections, an oracle and sqlserver. the dtspump task is pulling data from oracle to sqlserver. I am trying to automate this package by substituting the where clause values to variables since they are based on year and month. My question is - how does one use global variables in a dtspump task? How can I use variables to do this? Can it be done or do I need to use an Active X script for it?

thanks for any help.
novice userMay check with SQL DTS (http://www.sqldts.com) website.

Global Variables And the Script Component in DataFlow

I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.

I did add it to the readwrite variable list but I haven't been able to access it.

Type "Me.Variables." and upon typing the second period, intellisense will kick in and give you a alist of all the vars that you have put into ReadOnlyVariables/ReadWriteVariables.

-Jamie

Global Variables

Hello,
Im having a little problem(i hope) with global variables.
Im working with the DTS of the SQL SERVER 2000! Does someone knows how can i load global variables using an SQL Task!?
Thank you all!!
Kind Regards,
LULUin package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des|||Originally posted by DesmondX
in package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des

Thanks.... DESMONDX

Global Variables

Hi,

Is there a way to declare a persistent global variable in SQL Server?

I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.

For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:

IF @.MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.

Any other suggestions are also welcomed.

Thanks,

Skip.Wouldn't you just put this into a table? Then add the statement:

DECLARE
@.Environment bit

SELECT @.Environment = Environment FROM tblFlags

IF @.Environment = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.

Regards,

hmscott|||First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.

Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.

Any other suggestions?

Thanks,

Skip.|||Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.

You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?

We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.

Regards,

hmscott

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.

Global Server Variables

Is there a way in SQL Server to setup Global Server Variables or Constants

We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.

It would be great to be able to set a global variable and use it anytime that code is needed.

Any ideas.The way I have accomplished this myself is to use a UDF (user-defined function). This will give you just one place to make the change.

CREATE FUNCTION [dbo].[getPhoneType]
(
)
RETURNS int AS
BEGIN
RETURN 731
END

To use it:

SELECT * FROM myTable WHERE PhoneType = dbo.getPhoneType()

Terri|||Since you've created another db to hold your add-ons, you could build tables to hold those type of values. I usually create a name/value table to hold things like key/value pairs and then just select the correct record from it when I need it.

Global and Shared variables of Crystal

I have been converting a crystal report to MSRS. Crystal allows sharing variables (called shared variables) between main reports and it's subreports. Also, it has a concept of global variables which are available throughout a report. Many decisions (e.g. hiding the footer of a report) are based on these type of variables.
How can we achieve this in MSRS ? Do we have something i can add to Report Code which works as a global variable/shared variable Or there is any worwround available in general.
Imperative to mention that i shall be using seperate datasets for subreports of CR for performance reasons as suggested in BOL.
Thanks.Variables can not be shared across multiple reports. You can pass parameters
between reports however...When you call a subreport, pass whatever
information you need as parameters.
You can also use parameters as global variables within a single report...
SP1 allows parameters to be hidden so they do not appear on the user
selection list... Create the parameter and hide it... Then use the
Parameters collection to refer to it...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"push" <push@.discussions.microsoft.com> wrote in message
news:3E941DF6-2AEE-4F24-9145-D7A5CCDDDDAB@.microsoft.com...
> I have been converting a crystal report to MSRS. Crystal allows sharing
variables (called shared variables) between main reports and it's
subreports. Also, it has a concept of global variables which are available
throughout a report. Many decisions (e.g. hiding the footer of a report) are
based on these type of variables.
> How can we achieve this in MSRS ? Do we have something i can add to Report
Code which works as a global variable/shared variable Or there is any
worwround available in general.
> Imperative to mention that i shall be using seperate datasets for
subreports of CR for performance reasons as suggested in BOL.
> Thanks.