March 16, 2004 at 8:30 am
I'm new to DTS and am trying to export some table information to an Excel spreadsheet. While I have it working ok, I have an issue where one of the columns I'm exporting only needs to exist for one specific customer. I was trying to write a column transformation checking the value of a global variable for my customer value. Move the column if it matches
else no column transformation at all. How do
you write the "no transformation at all"?
Thanks for your help!
March 16, 2004 at 12:32 pm
write an activex script like
if customer1 then
dtsdestination("col1")=dtssource("col1")
This will only be executed if the global variable matches that customer. There is no need to specify no transformation condition.
March 16, 2004 at 12:48 pm
Thank you for the reply. I tried this and it doesn't like that the column doesn't exist in the destination file. For one customer the column will exist and all others it will not. I should probably just make a separate DTS package for the special customer?
March 16, 2004 at 2:13 pm
can you post your code and the error that you are getting?
March 16, 2004 at 2:22 pm
Here is the transformation code:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
if DTSGlobalvariables("Customerid").value = "3245" then
DTSDestination("note ") = DTSSource("note")
end if
Main = DTSTransformStat_OK
End Function
Here is the error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147213304 (80042008) Error string: Column name 'note ' was not found. Error source: Microsoft Data Transformation Services (DTS) Data Pump Help file: sqldts80.hlp Help context: 0 Error Detail Records: Error: -2147213304 (80042008); Provider Error: 0 (0) Error string: Column name 'note ' was not found. Error source: Microsoft Data Transformation Services (DTS) Data Pump Help file: sqldts80.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
March 16, 2004 at 3:26 pm
How are you setting the value of customerid? Are you trying to write to an existing excel spreadsheet?
March 17, 2004 at 8:05 am
The customer id is being set with a global variable which I am supplying with the \A parameter on DTSRun. It's working since I see the value in the email I'm sending with the file attached.
For the Excel spreadsheet...I have two templates set up with the file layout. Before running the DTS package, I'm copying the template to create a new blank excel spreadsheet. Here is the code for the copy that I'm performing in a stored proc:if @customerid = '3245'
begin
set @delcmd = 'del \\Servername\folder\siteguidechanges' + @customerid + '.xls'
set @copycmd = 'copy \\Servername\folder\siteguidechg3245template.xls \\sssvr10\public\siteguidechanges' + @customerid + '.xls'
end
else
begin
set @delcmd = 'del \\Servername\folder\siteguidechanges' + @customerid + '.xls'
set @copycmd = 'copy \\Servername\folder\siteguidechangestemplate.xls \\sssvr10\public\siteguidechanges' + @customerid + '.xls'
end
exec master.dbo.xp_cmdshell @delcmd
exec master.dbo.xp_cmdshell @copycmd
Is this the problem? Should it not be created first? Thanks again for taking the time to help!
March 17, 2004 at 8:52 am
Is the column name really "Note "?
If you look at the DTS error, it does not like all the spaces after the word Note.
March 17, 2004 at 8:56 am
You know, I never questioned that since it was generated (the code) by DTS. I will take the spaces out of the transformation code and try it. Thanks for the suggestion.
March 17, 2004 at 8:58 am
Check out this article
March 17, 2004 at 9:15 am
Thanks for the link. It has some excellent suggestions. I'm doing a stored proc and using a global variable as a parameter, which works well to get the data.
This process actually works fine if the Note column exists in the excel spreadsheet. If it doesn't exist in the destination spreadsheet, it gives me that error above. I was hoping the transformation code would ignore the fact that it wasn't there since I didn't want it anyway (does that make sense?).
March 17, 2004 at 10:42 am
What if you create a dts package with two transform data tasks, one for customerid 2345 with the note field and another for all other customers. Then your excel spreadsheets could have different structures and everything would work fine.
March 17, 2004 at 11:04 am
I'll give that a try, thanks again for all your help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply