DTS Run - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS Run

Author

Topic

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-06-04 : 04:21:31

hi

i am doing a telecom project i have huge datafile generated which i need to load in tables and do billing..

i am using Dtspackage and bulkinsert object it work fine.
my datafile for bulkinsert should be parameterized so that it can be change every month....

i add a global varibles and through ActiveX Script object and
i am trying to change the datafile

DTSTask_DTSBulkInsertTask_1.datafile=DTSGlobalVariables( "Sfile" ).Value

in simple... how do i change the datafile of bulkinsert of object. using globalvaribles

thanks

======================================
Ask to your self before u ask someone

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-06-04 : 05:56:08

i tryied something diff... but this to gives error... any help
please

SET QUOTED_IDENTIFIER off
declare @sql varchar(1000)
set @sql="BULK INSERT data_dmp.dbo.fix_temp FROM 'D:\Datafiles\fix_010102.dat' WITH (FORMATFILE = 'D:\Datafiles\fix.fmt') "
print @sql
exec @Sql

i get the error

 Server: Msg 203, Level 16, State 2, Line 4
The name 'BULK INSERT data_dmp.dbo.fix_temp FROM 'D:\Datafiles\fix_010102.dat' WITH (FORMATFILE = 'D:\Datafiles\fix.fmt') ' is not a valid identifier.

if execute the output then it work fine...

thanks

======================================
Ask to your self before u ask someone

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-04 : 10:04:37

Don't use double quotes
set @sql='BULK INSERT data_dmp.dbo.fix_temp FROM ''D:\Datafiles\fix_010102.dat'' WITH (FORMATFILE = ''D:\Datafiles\fix.fmt'') '

HTH
Jasper Smith

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-06-05 : 01:46:03

thanks
jasper_smith using double quotes is not a problem
i tried with exec sp_executesql @sql and it works...

but one thing i was not able find and non of sql guru replyed about DTS packages....

once more how can i change the datafile for bulkinsert in dts package using global varibles any one....
thanks

======================================
Ask to your self before u ask someone

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-06-05 : 02:18:40

Which version are you using?

If you have SQL2K then look into the "Dynamic Properties Task"

This Task can set just about everything....

Then pass in the Global variables...

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources