May 11, 2006 at 7:28 am
Hi,
Currently I have two SQL tables with different structure
I have to transfer the records from each SQL table to corresponding text file.
That is for example I have
T1 and T2 SQL tables with different structure
When I am executing the DTS, the DTS has to transfer the records from T1 SQL table to C:\Temp\T1\t1.txt
and transfer the records from T2 SQL table to C:\Temp\T2\t2.txt
I am using global varibles for the following items:
SQL Table,
Server Name,
Database Name
Destination Location
Destination file
It is working fine if I define two different DTS.
But I want one DTS to do the above job
Can any one please help me
Thanks
May 11, 2006 at 3:51 pm
If you want both of these to run at the same time then create yourself 3 datasources. 1 for you SQL Server (Source) and 2 texts files for your destinations.
Create a datapump from the SQL Source Server to the 1st text file destination (C:\Temp\T1\t1.txt) and populate that with whatever SQL you are using for t1, transformations etc. Then create a seperate datapump from the same SQL Server Source to the 2nd text file destination (C:\Temp\T2\t2.txt) with the T2 SQL in it.
Not certain how you are using your global variables and so on but when I do this I have the text file destinations populated by a Dynamic Properties Task, based on an ini file in the directory or another table somewhere else. In your example it might simply be a case of creating another set of variables for the 2nd datapump and telling the Dynamic Properties Task to use them for the 2nd text file destination?
When you run it will carry out both datapumps at the same time....
Unless this is what you've already tried and there's some problem with the global variables that I'm mis-understanding?
Edited after post as it keeps turning my brackets into smileys
May 12, 2006 at 9:02 am
Hi Mike,
Thanks for your reply
Yes I tried the option already as you mentioned. But my requirement is not only two tables. It is n number of tables
That is currently I am working on a interface project. Some of the table values need to be populated to text files.
Currently four tables in my lap. It may increase in future. What I need is One source and one destination and one datapump
In the Datapump I can define a default table settings (Source SQL table and Destination Location and the Destination Text file name and the Transformations)
What I tried was, just created as above and created one Activex file
By using the Global varibale/Dynamic Property, i did the follwoing code
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
If DTSGlobalVariables("gUserCode").Value="D" Then
DTSDestination("Patient_Number") = DTSSource("Patient_Number")
DTSDestination("User_Code") = DTSSource("User_Code")
DTSDestination("FL_Admission_Number") = DTSSource("FL_Admission_Number")
DTSDestination("FL_Sub_Contract_ID") = DTSSource("FL_Sub_Contract_ID")
DTSDestination("FL_Provider_Info") = DTSSource("FL_Provider_Info")
DTSDestination("FL_Nationality") = DTSSource("FL_Nationality")
DTSDestination("FL_Race_Code") = DTSSource("FL_Race_Code")
DTSDestination("FL_Gender") = DTSSource("FL_Gender")
DTSDestination("FL_SSN") = DTSSource("FL_SSN")
DTSDestination("FL_Employer_Id_Number") = DTSSource("FL_Employer_Id_Number")
Main = DTSTransformStat_OK
ElseIf DTSGlobalVariables("gUserCode").Value="P" Then
DTSDestination("User_Code") = DTSSource("User_Code")
DTSDestination("FL_Disability_Income") = DTSSource("FL_Disability_Income")
DTSDestination("FL_Prognosis") = DTSSource("FL_Prognosis")
DTSDestination("FL_Legal_Status") = DTSSource("FL_Legal_Status")
DTSDestination("FL_Admit_Type") = DTSSource("FL_Admit_Type")
DTSDestination("FL_Days_Spent") = DTSSource("FL_Days_Spent")
DTSDestination("FL_Days_Worked") = DTSSource("FL_Days_Worked")
DTSDestination("FL_Employement_Income") = DTSSource("FL_Employement_Income")
DTSDestination("FL_Subsidy_Income") = DTSSource("FL_Subsidy_Income")
DTSDestination("FL_Other_Income") = DTSSource("FL_Other_Income")
DTSDestination("FL_GAF_Score") = DTSSource("FL_GAF_Score")
DTSDestination("FL_Total_School_Avail_Days") = DTSSource("FL_Total_School_Avail_Days")
DTSDestination("FL_Total_School_Attend_Days") = DTSSource("FL_Total_School_Attend_Days")
DTSDestination("FL_CGAS_Score") = DTSSource("FL_CGAS_Score")
DTSDestination("Assessment_Sequence_Number") = DTSSource("Assessment_Sequence_Number")
DTSDestination("Element_Code") = DTSSource("Element_Code")
DTSDestination("Key_Column") = DTSSource("Key_Column")
Main = DTSTransformStat_OK
End If
End Function
But after running the DTS I am getting the following Error in both the text files.
As follows:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_2
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_2
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'DTSSource'
Error on Line 6
Am i missing anything?. Can you please help
Thanks
May 12, 2006 at 9:17 am
The n tables... are they the same structure, just with differing data?
If so you could have 1 data pump with an interative loop controlled by your ActiveX.
Mike
May 12, 2006 at 10:18 am
Hi Mike,
No the SQL tables are of different structure
May 12, 2006 at 1:18 pm
I would be inclined to try something from the command line for something like this. It would look something like:
ISQL -SMyServer -E -Q"SELECT * FROM db.owner.MYTABLE" >> c:\temp\myfile.txt
Other options are available for the ISQL. Enter ISQL /? for details
Good Luck!
May 14, 2006 at 1:06 am
Hi Dennis,
Thanks for the reply.
I tried the command
ISQL -SAK -E -Q"SELECT * FROM BUT.dbo.Pat" >> c:\temp\myfile.txt
(AK is my server name and BUT is my DB and Pat is my table)
and I am getting the following errror:
But I tried the following command and it is saving the results in the output file
oSQL -SAK -E -Q"SELECT * FROM BUT.dbo.Pat" >> c:\temp\myfile.txt
But it is giving the Column names and do not want that to be in my Export file
Of Course if we execute the select query in the SQL Query analyzer and set the option as "Print Column Headers" Check box to off it is giving the results with no column headings.
But what option I need to add to off the column headings printing in the OSQL query
Thanks
May 14, 2006 at 1:18 am
Hi Dennis,
I checked the MSDN and there is an option for the above as -h-1 to not to print the column headings.
But query result saved in the file is breaking into two many lines (line separator). But I need the results should be saved in a single line for each row
In the oSQL help command it says its maximum size is 80 char. Is there any way to achieve the above
Thanks in advance
May 14, 2006 at 2:17 am
Thanks Dennis,
I checked the same in MSDN and it is mentioned the width option also as -w<n>
and it worked well.
Here is the statements I executed
set @query= 'select * from tablename '
set @FilePath='c:\temp\t.txt'
set @cmd = 'osql -S'+@Server_Name+' -E -h-1 -w1000 -Q'+ '"'+@query+'"'+ ' >> ' + @FilePath
--print @cmd
exec master.dbo.xp_cmdshell @cmd,no_output
Dennis Thanks a lot for the help
But still I am very curious about working with DTS for the same
It will be helpful if any one help me to understand how the problem can be solved using DTS.
Thanks a lot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply