Solved

Use SSIS variables as column in sql table

Posted on 2007-04-07
2
1,191 Views
Last Modified: 2013-11-30
I want to use the value of one of my SSIS variables as a column in a table I'm importing.  Basically, I have 36 files that I'm importing from Excel into a SQL table.  I want one of the columns in my sql table to have the filename in it.  Can someone help me out with this?
Jim
0
Comment
Question by:JLEmlet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 18874064
Two ways to do it would be using a derived column transformation or a script task. Either way, the transformation must be inserted between the data source and destination.

Derived column transformations are described in http://msdn2.microsoft.com/en-us/library/ms137630.aspx . Values are taken from expressions, which can include variables. In this case the expression would consist only of the variable, and is easily created in the expression editor.

I have usually used script tasks instead of derived column transformations because I usually need to include changing derived values such as identity values that start with a value taken from a variable and then increment for each row.

New columns are added to script tasks on the Inputs and Outputs tab, as I recall (I don't have access to BIDS at the moment). The 'inputs' portion lists the inputs to the script task, while 'outputs' lists columns outputs added by the task. Once you have added the column, you will need to add the appropriate variable name to the read-only variables list on the Script tab. Then within the script you can access the value through the Dts.Variables collection and assign it to the appropriate column. Some information can be found at http://msdn2.microsoft.com/en-us/library/ms136127.aspx .

If all you need to do is add a value taken from a column, the derived column transformation shoud be easier to add. Again, this is all from memory but I will be back in a place where I can run BIDS in the morning, if you need more information.
0
 

Author Comment

by:JLEmlet
ID: 18888873
thanks, that did the trick.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 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