?
Solved

Store a value in a variable

Posted on 2009-02-18
10
Medium Priority
?
1,607 Views
Last Modified: 2013-11-30
Hi,

I have a oledb command which is executing a store proc which has got an output value, I want to store that output value in a varaible in inegration services package.
Is there any way I can do that.
Thanks in advance.
0
Comment
Question by:intergy-ee
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23678063
Use the attached Doc
Test.doc
0
 

Author Comment

by:intergy-ee
ID: 23678160
Hi pratima,

Can i get that package example as i dont find any Derived Column option and I am getting error in script near Row.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 23678305
No I can't share package with you.
what error you are getting ?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:intergy-ee
ID: 23678345
ok..

I need the select statement/table which u were using in ole db source
I need to store proc which has the output column.
I need the whole script where we can assign to a variable.
sorry I am new to integration services.....

thanks

suman
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1000 total points
ID: 23679852
Here is a document I put together that explains the usage of variables in SSIS. Let me know if you have any questions.
Hogg

SSIS-Populate-Variable.pdf
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23679950
Follow Hogg Document! It's all there!!!
Regards,
Pedro
0
 

Author Comment

by:intergy-ee
ID: 23686658
Hi Hogg,

Thanks for ur document, it was really helpful for me, but my question was how can I assign a variable from olebd command object which executes a stored proc which will return a output value.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23687970
Sorry, OLE DB Command cannot return a value, it can only perform a SQL command.
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 1000 total points
ID: 23692131
I made an example for you.
Follow this steps:
1. Execute the attached SQL Stament in one SQL database
2. Create a new SSIS Project in Visual Studio or open an existent project
3. Add the attached package to the SSIS Project
4. Update teh connection to the source textfile to the attached file "Source.cities.txt"
5. Update the SQL connection in the package to the database where you excuted the SQL Statment in previous point 1.
6. Exceute the package and check the returned output from Database.

This project will insert cities stored in a textfile into a SQL Database tavle CityTable and return to the SSIS Package the returned ID (identity) of table.

helped?
regards,
Pedro


SSIS-Interface.JPG
CreateTable-and-StoredProcedure-.txt
Source-cities.txt
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question