June 26, 2006 at 2:21 am
Hi All,
I'm having a few problems with DTS and hopefully someone can help me out a bit.
The task i am trying to achieve is to import data from a number of Excel files (in the same format) to a SQL server database. I also need to add a column of data to each of the imports when copying to the Database.
I have a DTS package set up which has both a connection to and Excel file and one to the server. The workflow represents the query used to copy data from Excel to SQL. I have included a '?' to add the parameter for my additional field and set up the parameter to point to a global variable 'gv_URC_No'.
select `'Concession Log$'`.`Concession ID`, `'Concession Log$'`.`Concession Number`, ? AS URCID
from `'Concession Log$'`
where `'Concession Log$'`.`Concession Number`<>null
This does seem to work and takes the variable gv_URC_No' initial value and imports the correct data. I also have a second global variable 'gv_ExcelFileLocation' which i want to be the location of each Excel file that requires processing.
I've added a Dynamic Task Property and added both global variables. Setting the Connection1 data source to the Global variable.
The problem i'm having is that when i call DTSrun from the command line with arguments, it seems to always use the defaults in the DTS package itself as apoosed to the new arguments.
DTSRun /S "<servername>" /U "<username>" /P "<password>" /N "Concession Log DTS Package" /A "gv_ExcelFileLocation=c:\temp\routecard\3340A1021204\3340A1021204 Route Card.xls" /A "gv_URC_No=10666"
I run the above command line and still the DTS package uses the defaults set to the Global variables.
My final intention is to call this from a stored procedure passing in the two GV values.
Can anyone help me find out why this isn't working and where i'm going wrong.
Many thanks in advance.
Grant
June 26, 2006 at 3:12 am
Hi again,
Slight correction over the previous post. I can set up the gv_URC_No global variable. The problem seems to be coming from the file location variable. This doesn't seem to changing in the DTS package.
I have created another task exactly the same although this time i expost to another excel table with an additional field for "Source Path" and pass in the global variable arguments. The Source Path column in the excel field is set to the value of gv_ExcelFileLocation. This actually comes out as it should. So the problem is when i try to set the data location of the Excel (connection1) file to the global variable.
Hope this sheds some more light on the problem i'm having.
Thanks again,
Grant
June 26, 2006 at 9:21 am
Another update on the problem folks,
I have now figured out that i needed to link the Dynamic Properties Task to the data transform connection which i've now done. I also found a Prompt value for the connection which if i set to a constant of 3 allows me to enter the data source when prompted as the DTS package runs. This is fine and works. I still however cannot get the value of the Global Variable to just set the data source of the connection to the Excel file. Is this actually possible? i'm having my doubts now.
Thanks,
Grant
July 1, 2006 at 7:31 pm
Not sure if it is the whole problem but it looks like you are not passing the parameters to dtsrun correctly. The correct syntax is:
/A global_variable_name:typeid=value [where typeid = 8 for strings]
so your first parameter should be:
/A gv_ExcelFileLocation:8="c:\temp\routecard\3340A1021204\3340A1021204 Route Card.xls"
July 2, 2006 at 2:56 am
apologies, i should have posted back sooner but i got the solution. Filet, you are absolutely spot on. It was a bit of an oversight by me to include the typeid in the string. There were a few other discrepencies in the command line text. Its working now.
Cheers,
Grant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply