February 5, 2007 at 9:27 am
OK, I have the wierdest request. I have a file that I get from a vendor, who does not have any type of primary key value in the file. SO I have had to use an identity field...works fine.
HOWEVER, I am worried about duplicates in the event that the upload process is run again on accident...so I wanted to take the specific filename that I get when I receive the file and use that to populate a column in the table.
I can use a global variable already to get the files name...so that isn't a problem.
Thing is in the pump task to take the excel spreadsheet and upload it to the table I don't know how to use that global variable to assign it as one of the useable files...if anyone can help me out I would appreciate it.
Thanks,
Leeland
February 5, 2007 at 9:47 am
I the data pump, view the transformations. Delete your existing transformations and create a new one. You will be prompted for the type of transformation you would like to create, select ActiveX. Edit the new transformation by going to the source and destination tabs just as you normally would and set up the mappings between your spreadsheet columns and your table columns. Make sure you have the column that will hold the file name in the destination tab. Once you have completed this, go back to the General tab and select the properties button. This will auto-generate the ActiveX script based on the source/destination setup you've already done. What you'll need to do now is assign the global variable value to your column name. In the object browser on the left, expand the destination columns container. Find your column for the file name and double click it. This will move it to the script window. Do the same for your global variable and there you have it. The line for this should look like this: DTSDestination("YourColumn") = DTSGlobalVariables("FileName").Value.
February 5, 2007 at 10:01 am
Hi John,
Thanks man it worked out great!, I was one step off and didn't notice that Brower which would have made it a ton easier.
Thanks again,
Lee
February 5, 2007 at 10:04 am
Yes, the browser comes in very handy and saves you from having to remember (and type) all of your object names.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply