remove nulls with dts? 1

[patrichek]

Hello,
i have a dts package that imports data from ms access into a temp table then into my destination tbl. is there some code that will remove the nulls from the destination table once the data is imported?

thanks!

 

[AlexCuse]

Hi again pat. Try this (and repeat for as many columns as you have to update):

UPDATE [tblName]
SET [column1] = ' ', [column3] = ' ', [column7] = ' '
WHERE [column1] is null
AND [column3] is null
AND [column7] is null

Another idea would be to make it so your temp table has all fields present in the final destination table. THen for all columns that are not being imported from access, you could set a default value of ' '. Then have your DTS pick up all of these columns, so that it inserts the ' ' values into your production table. I would go with this option as there is less changing of anything in your production table.

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[patrichek]

thanks!
i went with the first option.

 

[AlexCuse]

I knew that ya would ;-)

I probably would too, but I get nervous giving people advice on what to do on their production tables. After the few days you've had I'm sure you are extremely familiar with that particular table though....

I also don't see how in your case you could end up with a non-null value in any of those fields.

Glad it helped,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

PS- did you figure out getting your DTS package to run through Access? I saw a couple of good posts on here and was able to get the command line to work. I think you could just shell the command through VBA.

thread961-1260960 has the piece of code you would need to send to the shell. Let me know how that works out for you, as it is something I plan to implement fairly soon...

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[patrichek]

Hi Alex,
I've been trying to use this code

Private Sub SendCmd_Click()
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
        ServerName:="The-server", _
        ServerUserName:="user", _
        ServerPassword:="*****", _
        PackageName:="TheDTSPackageName"
dtsp.Execute
End Sub

it works from my pc with Enterprise Mgr. installed but doesn't work on domain pcs. I've posted a new thread in dts and access vba forums.
What does your vba look like?

thanks,
P

 

[AlexCuse]

I haven't actually implemented it but I was going to do something like this

public function DTS()

Dim SV as Variant
Dim DQ, s, s1, s2 as String
Dim ServerName as String
Dim PackageName as String
Dim ExecutionLine as String

DQ = """"
s = "dtsrun /S"
s1 = "/E"
s2 = "/N"
ServerName = "PVSQL"
PackageName = "DTSTEST"
ExecutionLine = DQ & s & ServerName & DQ & " " & & DQ & s1 & DQ & " " & DQ & s2 & PackageName & DQ

SV = Shell(ExecutionLine)

End Function

I'm not sure if this will need the double quotes or not, but there are spaces so it probably will. Basically you want to send to the shell a command that looks like this:


dtsrun /SPVSQL /E /NDTSTEST

I believe that the command line method will work from a PC without enterprise manager installed, but this is another thing I'm not 100% on. You will certainly have to map the SQL server as a network drive and probably specify the entire path for dtsrun.

HTH,

Alex

PS - my SQL server authentication is based off my windows login, so my requirements are probably slightly different.


I'll test and get back to you, waiting on some LOOOOONG running sp's right now

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.