DTS copy truncates bigint values

I just discovered that a bigint column in one of my tables is getting it's values truncated when I run a DTS job to copy the data to another database. The DTS job is designed to refresh our test environments from our production database. All the other tables copy fine. This include another table that also contains a bigint column. But this one table consistenly has a problem where 60 or so records are translated from positive number to negative values. The only explaination I came up with was truncation at some point.

Anyone know of a way to fix or work around this?Is it the same 60 records, or different ones? Have you tried perhaps converting this column to a large varchar field, to see what happens to those records then? Unfortunately I don't know what this is off the top of the head, but these might help figure out what it is.|||The number of records will change every so often. I think I first noticed 64 and then it was 62. I refined my query and discovered that in reality I have 192 records out of 6025 records where the bigint field was mangled. I did a join between the two databases to find the mismatches.

One thing I noticed is that it appears to work when going from our production server to our test server. Those servers are SQL Server 2K Standard SP2 while my machine is SQL Server 2K Developer SP3.|||Well maybe if you copied it to your machine to a temp table with a varchar as the datatype (converting the bigint to varchar in your DTS), then converting it back (just altering the table may do the trick since bigint to varchar is an implicit conversion). That may be a workaround for your machine anyways.

DTS - Looping in a Data DrivenQuery

Hi Guys

I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET

I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.

Thanks in advance.

Cheers
Chris

The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********

' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")

DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))

DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))

DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database

' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")

Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )

DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript

If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If

If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If

If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If

string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop

Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......

Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub

Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub

in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)

Make sense??|||Hi Rokslide

I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.

It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,

Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.

Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)

Where are you working in Christchurch? I used to live and work there myself. :)|||Hi

I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.

Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.

Mmmmm country air... ;)

Let me know if you need any more help with that DTS package.

DTS - Access Commection Properties

Does anyone know what the different values are (and what they mean) on the "Database Locking Mode" column off of the advanced button on a microsoft access connection within a DTS package on SQL server 7.0 ?

It's a 4 byte signed integer type with a default value of zero

Cheers,

DuncanMaybe this (http://www.able-consulting.com/tech.htm) will help?

DTEXEC /Config

This was directly from BOL.

/Conf[igFile] filespec

(Optional). Specifies a configuration file to extract values from. Using this option, you can set a run-time configuration that differs from the configuration that was specified at design time for the package. You can store different configuration settings in an XML configuration file and then load the settings before package execution by using the /ConfigFile option.

Does this mean that I can specify which configuration file to use during runtime?

Or is just because I'm too desperate for that, I understood that way

Thanks

You are correct. You can specify which config file to use at run time via the command line switch /CONF|||

Phil Brammer wrote:

You are correct. You can specify which config file to use at run time via the command line switch /CONF

So if I use config1 as my package configuration file (using enable package configurations, this needs an absolute path and this is emdedded in the designer code, if I'm not wrong) during runtime I should be able to specify config2 as the package configuration file?

If this is possible, then I believe /conf flag ignores the config file passed.

Thanks

|||I believe if you are going to specify a config file from the command line, you would not use "enable package configurations".

If you have config1 in the enable package configurations dialog, and specify config2 on the command line, config2 will also be applied, but config1 should have precedence, I believe.|||

Let me try a sample package and see what happens.

Thanks for the clarifications, I wish BOL was more clear.

|||

I just tested this with a package that had a configuration (config1) set at design-time. The configuration was setting the value of a variable. I copied the configuration (config2), and changed the value that was being set. I then ran the package from the IDE and got the value from config1 (as expected). Then I ran it from DTEXEC, specifying config2 using the /conf switch and got the value from config2.

So it appears that configurations specified at runtime take precedence over the the configurations specified at design time.

|||

Can the config file be in the network?

|||

Karunakaran wrote:

Can the config file be in the network?

Sure, but it can't be a mapped drive if you are going to schedule the package.

You should be able to get away with \\server\share as the path to the config file, provided that the security on that network share is setup correctly.