Hi,
I have the foll TEXT file

id doc name
0 1 test
0 2 test2

I need to import it into a table with the same columns id,doc,name but I
need to pass my own value for the ID instead of using the values from the
textfile so is it possible to pass a parameter to the text file import. On my
DTS package I have a text file (source) object and OLEDB connection object
and they are connected with a data transformation but I don't see any field
to set up as a parameter of global variable. Any ideas?

Thanks

Yep sure you can do this

when you look at the transformations unmap the column that goes from the
text file to the destination.

No create a Global Variable as an integer variable and set the initial value
to 0

In your transformations again highlight the destination column NO source
columns and add a new Active Script transform.

in the code add

DTSGlobalVariables("Name of variable").Value = Cint(DTSGlobalVariables("Name
of variable").Value) + 1

DTSDestination("Column Name") = DTSGlobalVariables("Name of variable").Value




--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Chris" <Chris (AT) discussions (DOT) microsoft.com> wrote

Hi,
Where do I created the global variable> I don't see any button or field to
do so.

"Allan Mitchell" wrote:

right click on the package whitespace | Package Properties | Global
Variables

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Chris" <Chris (AT) discussions (DOT) microsoft.com> wrote

Hi,
I did and its is executing fine when run from dts, but the column id is
still 0. Now when I try to execute the package from query analyzer I get the
message "The command(s) completed successfully." but no data is sent to the
table. I am passing the veriable like this



EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("id").Value', 1234
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END


Am I missing anything?


"Allan Mitchell" wrote:

Hi,
I got it to work! There is just one thing I am having problem with. I am
executing this package from TSQL (Query Analyzer) and passing the variable on
executing the package I put a begin transaction then commit and rollback if
there is a problem but the package still executes when it should rollback. I
checked the property on the package and transaction is checked.

This is how I am executing the package


BEGIN TRANSACTION T1

Select @doc_no = str_inv_no from dbo.fil_sql
WITH (TABLOCKX )


SAVE TRANSACTION mySavepoint

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END


EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer( , , , "package")',
NULL

.....................

EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END


UPDATE dbo.fil_sql1 <----this table is deliberately mispelt to cause an error
SET str_inv_no = @doc_no + 1
WHERE str_inv_no = @doc_no


IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION mySavepoint

END
ELSE
BEGIN
COMMIT TRANSACTION t1
END

I get the error

Server: Msg 208, Level 16, State 1, Line 66
Invalid object name 'dbo.fil_sql1'.

but the package still executes. Why?





"Allan Mitchell" wrote:

Hi,
Instead of passing the value of the global variable from my tsql statement I
want to put everything in the package so is it possible to pot an 'sql task'
object in the package and then have it select the value from a table and then
pass that to the global variable and then have another 'sql task' that will
update a table by taking the value of the global variable and add 1 to it. So
the order would be

1. select value from table and pass it to global variable
2. import the text file, transform the data and pass the global uariable in
the id column
3. update another table with globalvariable + 1

I would perfer this way because I am having transacton locking and rollback
problems when I execute the dts package from tsql,passing a variable and then
updating another table.

Thanks

"Allan Mitchell" wrote: