Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

Passing native SQL to Teradata

I want to add a step to an SSIS package to passthrough native Teradata sql statements so that they can be executed on Teradata. Does anyone know if I can use an "Execute SQL Task" to accomplish this? It should be very similar to passing native Oracle SQL to an Oracle server.

Hi SigEta!

Yes, you should be able to use the Execute SQL Task to do precisely that.

-David

|||

Didn't want to spend hours writing SQL to find out that I was planning a picnic for a rainy day! I'll be back if we can't figure this out... Thanks!!

passing multiple values from parent to child package

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so?

Your perception is right.

Kevin Rodgers wrote:

The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

I may not be understanding you correctly; but I think with either 'parent package variables' or 'XML configuration file' you still need to create an entry in package configuration organizer for every property you want to override; so no difference there. The only difference is that in a XML file, yes you're right, multiple object-properties values can be defined.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

you mean to make the parent package 'aware' of the variables available in every child package...not sure how good that would be; but you can submit a suggestion to Microsoft:

http://connect.microsoft.com/feedback/default.aspx?SiteID=68

|||

Kevin Rodgers wrote:

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

The XML config file allows you to specify lots of configurations. A configuration is for a single property only.

Kevin Rodgers wrote:

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

That would mean the parent package needs to have some knowledge of what is in the pckage it is calling. That doesn't really fit with the concept of abstraction - which is what the ability to do parent-child packages is all about really (in my mind anyway). However that isn't a complete justification and I can see why this would be useful - perhaps you should submit the request at Connect?

Hope that helps.

-Jamie

Passing GUID into dtexec, invalid cast to a string variable. Solution?

I am getting an invalid cast specification when I call dtexec and try to /SET a user variable within the package that is defined as a string data type. Is there any solution to this? I have tried passing the GUID with {} w/o {} w/ '' w/ "" etc.. many variations... and still get an invalid cast specification. Is there a data type that I need to set the User variable to besides String? The User Variable is used to Select records from a SQL data source where the GUID is stored. I do not have an option of GUID data type for a User Variable.

Thanks for any help! Aaron B.

What does your SET look like, this works fine for me. I just set it, and check the value in a Script Task, just to ensure the value is being passed in OK.

/SET "\Package.Variables[StringVariable].Value";"{2B7045E0-F3D2-478a-BCC3-0E73858C59A8}"

|||

Thanks for your help i am using another field for the subquery which is not GUID and its working fine. Thanks for your help.

Passing Global Variables from a Execute Package Task

I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?Package 1:
Edit your Execute Package Task, and click on the "Outer Package Global Variables" tab. Select all the Global Variables you want available to Package 2. Save and close.

Package 2:
Edit your ActiveX Script Task and use the code below to access the variable:
DTSGlobalVariables("<global variable name>").Value|||Package1 doesnt have an Execute Package Task, Package2 does...

Package1:
ActiveX Script --> Connection1 -TransformData-> Connection2

Package2:
Exec Package1 -Success-> Send Success Email
'-Failure-> ActiveX (msgbox DTSGlobalVariables("sErrorMessage").Value) --> Send Failure Email|||Ooookay.. so substitute 2 for 1 and 1 for 2... done...|||Thats what I figured when you posted, but I tried that and got nothing.....I might have edited the reply since you looked at it...

When I run Package1 by itself I can see in the Package1 Properties that the Global Variable is being set properly from Package1.

The Execute Package Task in Package2 has the Outer Package Global Variable selected, but when I reference it with DTSGlobalVariables("sErrorMessage").Value in ActiveX Code it shows the variable as being empty.|||Just a update on this:

Seppuku was nice enough the help me via AIM. So far we have come to the conclusion that there is no builtin way for a child to pass data to the parent except maybe through file drops, or writing to a db table.|||Will attempt having the child write to a common table for communication...and will post the results here.

Passing error description to failure task in DTS

In my DTS package, is there any way to pass the task name and error description to another task that gets called on the task's failure?In my DTS package, is there any way to pass the task name and error description to another task that gets called on the task's failure?
I have a bunch of packages that implement the same "on error" task as an Execute Package Task (in other words, my error handling task is its own seperate package). In each package that implements that error task, I set a global variable with the task name whose failure I'm trapping. Then in the error handling task I read the value of the global variable to fill an email notification.

If I knew how to trap the actual error, either in a global variable or with script, I'd stick that in a global variable and do the same thing, so maybe if someone knows how to do that...

Passing data from db to ssis variable

Is there anyway to pass data from a sql database to a variable in a ssis package. I'm trying to get a few fields from a sql database into some variables in my package and send an email using these variables with the send mail task?

Thanks,

Execute SQL task can do this.|||I tried, but don't even know where to begin. is there a tutorial or something on the net some where?|||Have you searched this forum? Tons of examples.

Execute SQL task.

If you are selecting where the result is a single row:
Result Set - Single row. Result set tab: Result Name: 0 thru n based on the number of columns. Then simply select the variable name you need to map to.|||There is more than 1 row in my table and 4 columns. I looked at a couple of examples but can't figure it out.|||http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx|||

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

|||

mr4100 wrote:

I have everything set up, when i go to expressions in the send mail task which I have inside my foreach loop and populate the to,from,subject and body lines with my variables from the result set, nothing happens? Am I able to use the send mail task in a foreach loop like this?

Sure you can. What do you mean nothing happens?|||

sorry to keep bugging you and I appreciate your help and patience, but when I open my package the send mail task has error code can't find recipient. I have 4 expressions in this mail task:

toline--set to my to variable

fromline--set to my from variable

subject--set to my subject variable

messagesource--set to my body variable

From what i gather, it's not getting the data from the variables for some reason. Everything seems to look ok.

|||Perhaps you need to add a script task inside the foreach loop so that you can display (via "MsgBox()") the contents of the variables to actually verify that they have data in them.|||i tried that but i get an element is not found in the collection error.|||

mr4100 wrote:

i tried that but i get an element is not found in the collection error.

First, make sure that your variables are of package scope, not some other task scope.

And how did you reference the variables in your script task?|||

they are part of the package and not just a specific task, i also set the delay validation property to true for the foreach loop and mail task, and the error is cannot recognize recipient address. Here is my script code.

Public Sub Main()

Dim Message As String

Message = Dts.Variables("to").Value + " " + Dts.Variables("from").Value + ", " + Dts.Variables("subject").Value

MsgBox(Message)

Dts.TaskResult = Dts.Results.Success

End Sub

|||Try this instead:

Code Snippet

Public Sub Main()
Dim Message as String
Dim vars as Variables
Dts.VariableDispenser.LockForRead("to")
Dts.VariableDispenser.LockForRead("from")
Dts.VariableDispenser.LockForRead("subject")
Dim ToVar as String = vars("to").Value.ToString
Dim FromVar as String = vars("from").Value.ToString
Dim SubjectVar as String = vars("subject").Value.ToString

Message = ToVar + " " + FromVar + ", " + SubjectVar
MsgBox(Message)
Dts.TaskResult = Dts.Results.Success
End Sub

|||well, i get an error with this code as well something about object reference not set to an instance of object

Passing data between multiple Data Flows

OK, it's the first of the month...that must mean it's time for another dumb question!

I'm trying to "componentize" an SSIS package with multiple Data Flows, and I can't figure out how to get the output of the results from one Data Flow into another.

So, for example, at the end of one Data Flow, I have a Recordset destination, and I'm storing that into a variable. But I can't figure out how to access the contents of that variable in the following Data Flow. Can this be done, or am I going about this the wrong way?The best is to use a raw file destination and source.

You could use a script component to store the data in a variable however you want and then use a script component to retrieve the data.|||If you want to try the second option that Simon mentioed then there's some code here that might help you extract data from a variable.
http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

-Jamie|||

Jamie, thanks for the link, but it doesn't seem to apply in my situation, as I'm not sending the recordset to a Script Task, but rather a Script Component (as a source) in a Data Flow.

What I'm trying to do is break a package down into four or five separate Data Flows that I can enable/disable and route around as necessary--essentially creating a template package that I can apply to many different scenarios.

It seems like it should be a trivial thing to dump the data at the end of one Data Flow into a recordset and retrieve it at the beginning of another, but I can't figure out how to do it.

I suppose using raw files is one answer, but it seems like there has to be a more elegant solution, without writing files to disk.

|||John,
Use the recordset destination to dump the data into a recordset in memory. The code I linked to above can easily be used in a script source component to loop through that recordset and put values into the pipeline.

Your next question may be "if there's a Recordset Destination, why isn't there a Recordset Source?" The reason is that SSIS needs to know the metadata of the dataflow at design time which wouldn't be possible with a recordset Source.

-Jamie|||Jamie

But whats the point of being able to put it in and not get it out. Essentially your working round the fact so why can't SSIS.

Having a recordset source that you define the columns, whats wrong with that. Ok so it can't validate until execution time and that needs to be understood. Not providing a source component people are just going to write their own as you sort of have done.|||

Will there be a "typed dataset source" somewhere in the near future? The current situation with writing data, say half a gig, to disk only to read it again seconds later does not sound very performant. And writing one's own metadata in scriptcomponent or scripttask (in a language other than the preferred) without IDE support isn't a killer feature either :-/

With best regards,

Artus

|||

ArtusKG wrote:

Will there be a "typed dataset source" somewhere in the near future? The current situation with writing data, say half a gig, to disk only to read it again seconds later does not sound very performant. And writing one's own metadata in scriptcomponent or scripttask (in a language other than the preferred) without IDE support isn't a killer feature either :-/

With best regards,

Artus

It may not sound very performant but have you tried it? Its a proprietary file format and is very very fast indeed.

Even an out-of-the-box recordset source wouldn't be able to pick up its metadata from anywhere because that metadata doesn't exist until runtime.

If there's a way around that problem then i don't know about it.

-Jamie

|||

The problem I am currently struggling with is the vast amount of data unnecessarily written to disk. Let's keep the example with 500 MB in the recordset. If I had to write it the disk were the bottleneck for several seconds. Therafter some logi occurs (prior to my next dataflow task). And when I then read the data from the raw file I am again waisting time by reading data unnecessarily written to disk (and blocking every other disk-i/o on the machine).

I don't mind the data being held in RAM as I want it there for further processing later on anyway.

And a design time component where I can define my RecordSet's colums using drag-drop were a great improvement. Think of a "write schema to file" option of the RecordSet destination and a "read schema from file" option in the to be component RecordSet source...

With best regards,

Artus

|||

What is the scenario you are trying to enable, Artus? The first post in this thread was about componentization of data flows - we will be addressing that in the future, probably through other mechanisms. In your case you want to persist the results of a data flow in memory and then have another data flow pick up that data? Why? You really want to persist 500MB of data in RAM?

Thanks

Donald

Passing data between multiple Data Flows

OK, it's the first of the month...that must mean it's time for another dumb question!

I'm trying to "componentize" an SSIS package with multiple Data Flows, and I can't figure out how to get the output of the results from one Data Flow into another.

So, for example, at the end of one Data Flow, I have a Recordset destination, and I'm storing that into a variable. But I can't figure out how to access the contents of that variable in the following Data Flow. Can this be done, or am I going about this the wrong way?The best is to use a raw file destination and source.

You could use a script component to store the data in a variable however you want and then use a script component to retrieve the data.|||If you want to try the second option that Simon mentioed then there's some code here that might help you extract data from a variable.
http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

-Jamie|||

Jamie, thanks for the link, but it doesn't seem to apply in my situation, as I'm not sending the recordset to a Script Task, but rather a Script Component (as a source) in a Data Flow.

What I'm trying to do is break a package down into four or five separate Data Flows that I can enable/disable and route around as necessary--essentially creating a template package that I can apply to many different scenarios.

It seems like it should be a trivial thing to dump the data at the end of one Data Flow into a recordset and retrieve it at the beginning of another, but I can't figure out how to do it.

I suppose using raw files is one answer, but it seems like there has to be a more elegant solution, without writing files to disk.

|||John,
Use the recordset destination to dump the data into a recordset in memory. The code I linked to above can easily be used in a script source component to loop through that recordset and put values into the pipeline.

Your next question may be "if there's a Recordset Destination, why isn't there a Recordset Source?" The reason is that SSIS needs to know the metadata of the dataflow at design time which wouldn't be possible with a recordset Source.

-Jamie|||Jamie

But whats the point of being able to put it in and not get it out. Essentially your working round the fact so why can't SSIS.

Having a recordset source that you define the columns, whats wrong with that. Ok so it can't validate until execution time and that needs to be understood. Not providing a source component people are just going to write their own as you sort of have done.|||

Will there be a "typed dataset source" somewhere in the near future? The current situation with writing data, say half a gig, to disk only to read it again seconds later does not sound very performant. And writing one's own metadata in scriptcomponent or scripttask (in a language other than the preferred) without IDE support isn't a killer feature either :-/

With best regards,

Artus

|||

ArtusKG wrote:

Will there be a "typed dataset source" somewhere in the near future? The current situation with writing data, say half a gig, to disk only to read it again seconds later does not sound very performant. And writing one's own metadata in scriptcomponent or scripttask (in a language other than the preferred) without IDE support isn't a killer feature either :-/

With best regards,

Artus

It may not sound very performant but have you tried it? Its a proprietary file format and is very very fast indeed.

Even an out-of-the-box recordset source wouldn't be able to pick up its metadata from anywhere because that metadata doesn't exist until runtime.

If there's a way around that problem then i don't know about it.

-Jamie

|||

The problem I am currently struggling with is the vast amount of data unnecessarily written to disk. Let's keep the example with 500 MB in the recordset. If I had to write it the disk were the bottleneck for several seconds. Therafter some logi occurs (prior to my next dataflow task). And when I then read the data from the raw file I am again waisting time by reading data unnecessarily written to disk (and blocking every other disk-i/o on the machine).

I don't mind the data being held in RAM as I want it there for further processing later on anyway.

And a design time component where I can define my RecordSet's colums using drag-drop were a great improvement. Think of a "write schema to file" option of the RecordSet destination and a "read schema from file" option in the to be component RecordSet source...

With best regards,

Artus

|||

What is the scenario you are trying to enable, Artus? The first post in this thread was about componentization of data flows - we will be addressing that in the future, probably through other mechanisms. In your case you want to persist the results of a data flow in memory and then have another data flow pick up that data? Why? You really want to persist 500MB of data in RAM?

Thanks

Donald

Passing connection managers to subpackages.

Is there a recommended way to pass the connection managers from the parent package to the subpackages? I also noticed that the subpackages can't reference the same configuration file as I get error messages when I try to build the solution. After I changed the configuration file for each subpackage it worked just fine. Did I miss something or is this intended to work this way?
thanks

Hi,

when you say "passing the CM's from the parent package to the subpackages", you use the ExecutePackage task or a different method?

The error with using the same configuration file is a known issue and will be addresed in a future realease

Thanks,
Ovidiu Burlacu|||I guess I'm having a difficult time understanding and even testing connection managers and configuration files. Here's what I'm trying to accomplish and am failing...

I have a project "X" with a "main" package and a another package called "child".
I have defined a generic connection manager in each of the packages (main and child ) called "SOURCEDB".

I have then created a configuration file called "connMgr". In both packages I identify this configuration file (let's just use direct configuration for now) and specify that I want the connection string, username and password configurable at load/run time.

My understanding would be that before I run the package I edit the configuration with the appropriate connection string and username so I can run the main package against different databases based on current requirements.

However, when I run the program it says that the configuration file is the same (during build) and generates an error. What is the recommended way to get around this and am I going about this the wrong way?

I have also noticed with indirect configurations that once you have to make a modification to the configuration file the file location initially specified for the configuration file is lost. Is that a bug also?

thanks
John|||1. To avoid the build error r-click the project in Solution Explorer window and choose 'Properties'. In that dialog go to 'Deployment Utility' and Set CreateDeploymentUtility to False.

If you really need deployment utility then use the XML configuration file only for the parent package and in the child package use a Parent Package variable configuration. Then in the parent package you create a variable where you can put your connection string.

2. I suggest you configure only the ConnectionString, because that will take care of User and Password as well.

3. You said "I have also noticed with indirect configurations that once you have to make a modification to the configuration file the file location initially specified for the configuration file is lost. Is that a bug also?"
Can you be more detailed in the steps that I need to follow to repro this problem?

HTH,
Ovidiu Burlacu|||Thanks for the info. I'll try answer 1. I tried using only connection string but I have noticed that the password doesn't get stored in the configuration file. When I change the connection string the job will fail the next time as the password isn't valid. How do I get the password changed and where when using the configuration file?

3. When I create an indirect configuration file I start first by creating the direct configuration which I point to a filename and directory. Once I have that created I go back and specify the env variable to use for this configuration.

Let's say I then have to make a modification to the configuration file. If I go back and uncheck the "Configuration location is stored in an environment variable" to make configuration changes I have to check the other option but the file name and directory are no longer there. I then have to go search for the configuration file location which is not too much pain for me but for someone else it could be who is administering the packages... It would be nice to retain the filename and location in that field.

thanks|||

With respect to your answer 1 I assume that's just a current workaround, correct? I assume that this is a bug and will be fixed in the near future ...

|||Yes this is a workaround, and as I specified in my first reply the bug will be addressed in a future release.

Thanks,
Ovidiu|||Do you have any thoughts on the password problem I'm having? I'm not sure if you saw the post from this morning ...
thanks|||

I tried your scenario but when I went in the XML config file and added Password=<my_password> to the ConnectionString the package executed just fine.

HTH,
Ovidiu

|||I was under the assumption it would add the password automatically. There is one slight problem with this. If you add the password manually it gets overridden the next time you regenerate the configuration file. You'll have to remember to add the password manually every time!!!|||Adding the password automatically is a security violation. (disclosure of confidential information). The user must enter the password manually, this way he will make sure that the file will get the appropiate ACL's

Thanks,
Ovidiu|||I did notice that if you use the manifest to install your packages on the server that it will automatically add the password to the config file. That would seem to contradict the above statement somewhat.|||

Can you describe the issue in more detail?

What password is saved in the config file?

Thanks,
Ovidiu

|||

If you double-click on the manifest file and go through the package installation wizard you can add the values for password and username if that's what you have checked off in the config file. It will then go ahead and add the password='password' in the configuration file.

Someone else at our company didn't specify connection string but username and password and it seems to add the password in the configuration file. However, if you use connection string it won't.

|||

This falls into what I said: "The user must enter the password manually"

If the user enters the password value in the wizard I must save that value into the configuration file because this is what the user want. Is not that we automatically save the password in the config gile.

Ovidiu

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

http://msdn2.microsoft.com/en-us/library/ms403356.aspx

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

http://msdn2.microsoft.com/en-us/library/ms403356.aspx

sql

Passing a variable between a parent and child package doesn't work

Has someone managed to pass successfully a variable from a parent package to a child package? I’ve tried a zillion permutations and I can’t get it to work. The strange thing was that I was able to successfully do this with pre-RTM builds. Basically, what I am trying to do is:

1. The parent package has a variable, e.g. ExecutionID which I set using a script to System::ExecutionInstanceGUID. I verified that the variable is set correctly by dumping it to a SQL Server table.
2. I created a child package variable with the same name.
3. In the child package, I’ve created a parent package configuration that points to the ExecutionID variable.
4. I am trying to read the variable in a Derived Column Task in which I have a column linked to @.ExecutionID.

This doesn’t work. Step-by-step instructions from someone who managed to concur this will be greatly appreciated.

Oh, I didn’t have any luck hitting a breakpoint in a script task inside a child package with both in and out of process execution also.

Never mind. My configuration got messed up. I am still not sure what needs to be done to debug a child packge though. Ideas?

passing a boolean parameter to a package

hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE.

i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work.

what can i do?

thnxs

kats wrote:

hello at everybody well i have a problem. with in a package i defined a bollean variable with the name "x" and with a default value FALSE.

i created a configuaration file that contains the variable "x" as [user::x]. when i deploy the package to a server and i try to execute the package putting to the set values TRUE fror the x variable it doesnt work.

what can i do?

thnxs

Boolean values are either 0 or 1. Not True or False. True or False is a presentation layer feature. Pass in a 0 for false or a 1 for true and things should work just fine.

Pass variable value to DataReader Source

Dear All,

I have created a DTS Package in Integration Services 2005.

Within the DTS Package declared a variable named xxx and passed a value 1234.

In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined

Property = [DataReader Source].[sqlCommand]

Expression = Variable name.

Now in the DataFlow Task Canvas dropped DataReaderSource.

How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.

regards

Sufian

This post explains how to do that...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2009522&SiteID=1

|||

sorry , i have a diffrent problem.

I need to know how can i pass the variable value to the query supplied in the SQLCommand custom properties in datareader source on execution.

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

Regards

Sufian

|||

mohd sufian wrote:

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

You can't concatenate a string with an integer. You'll have to cast the integer as a string and THEN concatenate.

The cast operator is:

(DT_STR, <length>, <code_page>) variable_value

Code page will probably be 1252.

-Jamie

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

Pass back error count to Parent pkg

I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.

Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?

Thanks!

See the following topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20103&SiteID=1

|||I should clarify ... I want to know how to get the number of Errors. I already know how to pass the value back.
|||

Why don't you want to use the error information available through dts.*?

Here is a useful link (although it uses the dts.* approach):

http://www.developerdotstar.com/community/node/327

NOTE: you can then push these values to a variable and pass it up to the parent package...

|||

OK, ignore any parent/child aspects of my question.

Let's say your package has a bunch of errors, and sometimes you get a warning message:

The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1);

I want to know how to get that "6" value, (i.e. I don't need to know any specific error info). It's obviously stored within the package -- I just want access to it.

Initially I was thinking I could code my way into the Package and retrieve the Errors Collection; then use its Count property:

CurrentPackage.Errors.Count

But, there's no easy way to do that, which is why I'm here.

|||

You could put something in the OnError eventhandler that simply increments a variable every time it executes.

If another way exists, I don't know about it. perhaps Microsoft do. [Microsoft follow-up]

-Jamie

|||

The Package object support the "Errors" property which you can call the "Count" method. Unfortunately you cannot get access to the package object inside script task. This is a design decision. The only way to get to property is via the programming object model.

I am not aware of any workaround beside Jamie's suggestion.

|||Since you are calling this from a parent package, you could run the child package through a script task, instead of the Execute Package task. That way, you could access the Package.Errors property of the child package.|||

That's a neat idea, John. I hadn't thought of that. I was hoping to avoid recalculating a count that already existed, but for now Jamie's workaround seems to be the simplest solution so I'll go with that.

Thanks to all!

Pass a parameter to SSIS from .NET App

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

Very much possible.

Check out the example @. http://www.codeproject.com/useritems/CallSSISFromCSharp.asp

More details on "Loading and Running a Package Programmatically " @. http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Thanks,
Loonysan

|||Sweet! Thanks :)

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1