Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calling a variable in SSIS?

Status
Not open for further replies.

grayt26

Technical User
Jun 2, 2006
26
GB
Hi, I am fairly new to the SSIS thing here but I am having trouble calling a variable that I have created.


My package looks in a directory to import multiple excel files and to do this I use a FOREACH LOOP Container. I have put in that foreach loop a variable name and in the connection manager also and so far it imports all the excel files into sql table.

My problem is now is that I want the package to write to a table the name of all the excel files it has imported into SQL. I thought I would be able to do this within the FOREACH LOOP container but im struggling to find anything that can help me??

Any ideas would be great.

Thanks
 
I'm fairly new to this as well but I think.....

You should be able to do this with a FOR..EACH LOOP.

Select loop type as 'File Enumerator' and map te variable to the file name.

You can then stick an SQL Task in the loop to take the file name variable and add it to a table.



Dazed and confused
 
Drop a SQL task within the FOR EACH loop and pass the SSIS parameter to the SQL command as a variable.

What connection type (ADO, .NET, etc) are you using? That will determine how you setup the variable to be passed to the SQL Statement.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Thanks,

I have tried putting an sql task within the foreach loop but i am still having problems. First I called the variable within the sql code and it wanted me to declare that variable first in the code so I take it that it is not seeing the variable from within the code? I take I am missing a step somewhere?
 
You have to map the variable on the Variables section of the SQL Task.

Can you post screenshots of the variable section of the SQL Task and your SQL command?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Hi all,

I have been working through my variable problem with some success with your help. I can get my sql statement to store the result to a variable. However my SQL statement is a select statement returning multiple rows. From what I have read on the MS help I have to set my result set type to Full Result Set and in the result set options I have called my result set name 0 and have placed my variable in the opposite box. Also I have made the variable type an object as the help describes but now it wants me to use an expression to support this?

Any ideas?
 
Hi think I am having a slightly different problem.

I beleive that the sql statement part is working fine. I was running the whole package and I beleive that there is something else in the package that does not like the fact that my variable is an object.

What I would like to do is test that the variable is holding the data correctly. Get some sort of output from it. I have tried a derived column but that really does not like putting my variable in there. Any ideas?

Thanks
 
You can't put your variable there because it is held in a object format like a ADO Recordset. if you want to test it you can create use the For Loop and and break on every iteration and check the non object variable that is required for the loop in the locals.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
 
I recommend you read the following website.

I struggled understanding how to get varibles out of a recordset until I'd read it.


Fundementally you create a recordset variable that has package scope and then map to a single column in the variable recordset on the FOR loop to another package scope variable you can then use in other tasks within the loop.


Dazed and confused
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top