Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.

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

Pass sysdate value to Global Variable

Hi,

How can we pass sysdate value as default value to a GlobalVariable to SSIS?

Regards,

Mohammad Sufian

Create global variable.

Set "EvaluateAsExpression" to true in the properties list for that variable.

In the expression box, type in "getdate()"

pass filename

Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks

I've achived something similar to this by doing the following:

(i've assumed you've already got to the stage of populating the variable with the name of the file)

Within your loop:-

1) add an execute sql task and enter the following in your SQLStatement section: - EXEC yourproc ? (the ? is the placeholder for the parameter)

2) set up the parameter to your variable in the Parameter Mapping section remembering to set the correct type. I don't believe the parameter name is important in this scenario.

Hope that makes sense.

|||

This is the error I get:

[Execute SQL Task] Error: Executing the query "exec uspMarketValuesUploadXMLfileReader ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||Did you fill out the parameter mapping tab?|||

Yes. Still the same error.

These are my settings

SQLSourceType: DirectInput

ParameterMapping --> User::FileName

datatype: varchar

|||Use a "0" for the parameter name. Also, you won't be able to parse the query.|||

Ah, so it looks like my comment about parameter name not mattering is not quite correct. Apologies. In the parameter mapping section, set the parameter name to be a valid sql parameter eg @.filepath. Hopefully that should do the trick.

Cheers

|||

Solved.

Had to replace 0 with ?

Thanks guys.

|||

Don't forget to mark posts as answered...

Good luck!

pass all data in a file as a string into a stored procedure

Hi,
Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure.
Thanks

Use the For Each Loop container to enumerate the files. I'd probably just use a Script Task top open the file and grab the data into a variable. Use the Exec SQL Task to call the procedure with a parameter mapped to the variable.

If it was large amounts of data then I'd probably put the procedure call in the script too to more effectively handle the large size, and save passing it around as much. Use an ADO.NET connection manager, you can work with this in the script.

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

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