Pick a file to import?[RESOLVED]

[brendalisalowe]

Does anyone know if there is a way to be able to select a file you want to import if you already have a DTS setup? This is what I have right now:

Dim excel As String = "exec master.dbo.xp_cmdshell 'dtsrun -E -S crserver -N High_Accurint'"
            Dim SqlConn56 As SqlConnection = New SqlConnection(strConnectionString)
            Dim cmdSqlCommand56 As SqlCommand = New SqlCommand(excel, SqlConn56)
            Try
                cmdSqlCommand56.Connection.Open()
                cmdSqlCommand56.ExecuteNonQuery()
            Catch ex As SqlException
                MessageBox.Show(ex.Message)
                Exit Sub
            Finally
                If SqlConn56.State = ConnectionState.Open Then
                    SqlConn56.Close()
                End If
            End Try

If there is no way to do this, I can just manually change the name of the file everytime, but who wants to do that.

Any ideas?

[Granty]

I dont have the code with me but try at SQLDTS.com

[brendalisalowe]

I went to this website (http://www.sqldts.com/default.aspx?208,1), but am having trouble figuring out how to do it, as usual. Can anyone help me out? Thanks!

[Granty]

Whats the prob? the bit you want is on page 3


' Set Filename
Set oConn = oPKG.Connections("TextFile")
oConn.DataSource = txtFilename.Text
Set oConn = Nothing

[brendalisalowe]

But where do I put that coding? This is what I have right now:

Dim excel As String = "exec master.dbo.xp_cmdshell 'dtsrun -E -S crserver -N High_Accurint'"
        Dim SqlConn56 As SqlConnection = New SqlConnection(strConnectionString)
        Dim cmdSqlCommand56 As SqlCommand = New SqlCommand(excel, SqlConn56)
        Try
            cmdSqlCommand56.Connection.Open()
            cmdSqlCommand56.ExecuteNonQuery()
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
            Exit Sub
        Finally
            If SqlConn56.State = ConnectionState.Open Then
                SqlConn56.Close()
            End If
        End Try

My file is called High_Accurint, but every month it changes. How do I make so I can pick the file? Thanks for your help!

[brendalisalowe]

How does the package get changed? Anyone have any ideas?

Thanks!

[Granty]

I have code at work, I will post an example for you tomorrow.

Basically, just connect to your SQL Server, set a reference to the package then change the target of the connection in question and execute it.

[Granty]

Dim cnn As DTS.Connection
Dim pkg As New DTS.Package
Dim objstep As DTS.step
      
  pkg.LoadFromSQLServer "ServerName", "UserName", "Password", DTSSQLStgFlag_Default, , , , "PackageName"
  
  Set cnn = pkg.Connections("TextFileImport")
  cnn.DataSource = "MyFilePath"
  Set cnn = Nothing
  
  For Each objstep In pkg.Steps
  
    objstep.ExecuteInMainThread = True
  
  Next objstep
    
  pkg.Execute
  pkg.UnInitialize

You need to replace everything in quotes with the names of your parameters

Hope that helps

[brendalisalowe]

It doesn't like this:

DTSSQLStgFlag_Default, , , ,

It puts a blue squiggley line under it.

Also, it doesn't like this:

pkg.Connections("TextFileImport")

Any idea why?

What do I put in here?: ("TextFileImport")

Do I use OpenFileDialog?

And this: "MyFilePath"

Thanks for your help!

[Granty]

You need to replace "TextFileImport" with the name of the connection between the database and the file you are importing from your DTS package. When you create a DTS package from the wizard, this is usually called "Connection 1"

MyFilePath is just the path of the file you wish to import. Best to use a dialogue or whatever in another sub and pass the selected filepath into this sub.

The pkg.LoadFromSQLServer method requires several parameters. One is that DTSSQLStgFlag_Default constant and some others are optional, hence the , , , ,

To be honest I really dont know the ins and outs of this very well I got all my info from the SQLDTS.com site.

[brendalisalowe]

For this: pkg.Connections

It says" Interface 'DTS.Connections' cannot be indexed because it has no default property."

Do you know what that means?

This is what I have so far:

Dim cnn As DTS.Connection
        Dim pkg As New DTS.Package()
        Dim objstep As DTS.Step
 
        pkg.LoadFromSQLServer("CRSERVER", "Administrator", "*******", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "Accurint_High")
        Dim ofd As New OpenFileDialog()
        ofd.ShowDialog()
        cnn = [color=red]pkg.Connections[/color](ofd.FileName)
        cnn.DataSource = "data source=CRSERVER;initial catalog=crdatabase;integrated security=SSPI;persist security info=False;workstation id=CRSERVER;packet size=4096;"
 
        cnn = Nothing
 
        For Each objstep In pkg.Steps
 
            objstep.ExecuteInMainThread = True
 
        Next objstep
 
        pkg.Execute()
        pkg.UnInitialize()

What is wrong? Any ideas?

[Granty]

Hmmm ok, how to explain it......

If you import a text file with the DTS wizard and then save the package, then look at the diagram of your new package you have a picture of a file and a picture of a sort-of computery thing with an arrow between them. If you look at the properties of the picture of the file, its name is "Connection 1". You can rename it.

That is the name you want to put in:

cnn = pkg.Connections("Connection 1")

Sorry I cant be more helpful, i work elsewhere now and we dont use SQL Server so im doing this from my (famously poor) memory.

[brendalisalowe]

Can anyone see why the line in red won't work?

Dim cnn As DTS.Connection
        Dim pkg As New DTS.Package()
        Dim objstep As DTS.Step
 
        pkg.LoadFromSQLServer("CRSERVER", "brenda", "*********", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "Accurint_High")
        Dim ofd As New OpenFileDialog()
        ofd.ShowDialog()
        [color=red]cnn = pkg.Connections("Connection 1")[/color]
        cnn.DataSource = ofd.FileName
        cnn = Nothing
 
        For Each objstep In pkg.Steps
 
            objstep.ExecuteInMainThread = True
 
        Next objstep
 
        pkg.Execute()
        pkg.UnInitialize()

Thanks for any help

[Granty]

It needs to be SET cnn = blah blah blah

[brendalisalowe]

Everytime I type in the word SET, it takes it away. Like it doesn't like it. Do I need to put quotes or anything?

Thanks for all your time Granty!

[Granty]

Sorry Brenda I dont know why it would do that

The code I posted above was my working code.....

[Granty]

Have you added all the references for SQL DTS to your project? That may be the problem (grasping at straws a bit here)

[brendalisalowe]

What references need to be added? I have the DTS reference in it already.

[brendalisalowe]

Has anyone ever heard of sp_update_job? Is that what I am supposed to use?

[Granty]

I added the following refs when I did this:

Microsoft DTSDataPump Scripting Object library
Microsoft DTSPackage Object library
Microsoft DTS Custom Tasks Object library

[brendalisalowe]

I think I am gonna go crazy. Does anyone have any idea why this won't work:

'change DTS Package
        Dim oPKG As DTS.Package, oStep As DTS.Step
        oPKG = New DTS.Package
        Dim oConn As DTS.Connection
 
        Dim sServer As String, sUsername As String, sPassword As String
        Dim sPackageName As String, sMessage As String
        Dim lErr As Long, sSource As String, sDesc As String
 
        ' Set Parameter Values
        sServer = "CRSERVER"
        sUsername = "brenda"
        sPassword = "g_Password"
        sPackageName = "Accurint_High"
 
        ' Set Filename
[color=red]        oConn = oPKG.Connections("Connection 1")[/color]
        Dim ofd As New OpenFileDialog
        ofd.ShowDialog()
        oConn.DataSource = ofd.FileName
        oConn = Nothing
 
        ' Load Package
        oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _
            DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName)
 
        ' Set Exec on Main Thread
        For Each oStep In oPKG.Steps
            oStep.ExecuteInMainThread = True
        Next
 
        ' Execute
        oPKG.Execute()
 
        ' Get Status and Error Message
        For Each oStep In oPKG.Steps
            If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
                oStep.GetExecutionErrorInfo(lErr, sSource, sDesc)
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Failed" & vbCrLf & _
                    vbTab & "Error: " & lErr & vbCrLf & _
                    vbTab & "Source: " & sSource & vbCrLf & _
                    vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
            Else
                sMessage = sMessage & "Step """ & oStep.Name & _
                    """ Succeeded" & vbCrLf & vbCrLf
            End If
        Next
 
        oPKG.UnInitialize()
 
        oStep = Nothing
        oPKG = Nothing
 
        ' Display Results
        MsgBox(sMessage)

It doesn't like the line in red Any ideas?

[vb_dba]

Wouldn't you need to Load the package first, then set it's connections?

[brendalisalowe]

Like this:

Dim ofd As New OpenFileDialog
ofd.ShowDialog()
oConn.DataSource = ofd.FileName
oConn = oPKG.Connections("Connection 1")
oConn = Nothing

It doesn't like that either. Any other ideas?

Look at this website:

http://www.sqldts.com/default.aspx?226

I think I need something like this, but can't figure out how to do it.

[vb_dba]

Without testing it, it seems like it should be:

' Load Package
        oPKG.LoadFromSQLServer(sServer, sUsername, sPassword, _
            DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName)
 
       ' Set Filename
        oConn = oPKG.Connections("Connection 1")
        Dim ofd As New OpenFileDialog
        ofd.ShowDialog()
        oConn.DataSource = ofd.FileName
        oConn = Nothing

Load the package first, then try to set the connection source property

[brendalisalowe]

Good thinking, but it still doesn't like it. Want me to send you my code? I am using VS.NET 2003.

[vb_dba]

Is it still failing at the oConn = oPkg.Connections("Connection 1") line?

[brendalisalowe]

It doesn't let me run it because there is a blue squiggley line under it. When I put my mouse over that line it says:

"Interface 'DTS.Connections' cannot be indexed because it has no default property."

Ever seen that before?

[vb_dba]

Try change this line:

oConn = oPKG.Connections("Connection 1")

to:

oConn = oPKG.Connections.Items("Connection 1")

[brendalisalowe]

Well, it is kind of working now. It runs through the code and even lets me pick the file to import, but it always just imports the file that is saved in the DTS Package. Shouldn't it be changing the Excel file that I pick? Any ideas?

[vb_dba]

You would think so....


Try changing the following block of code:

oConn = oPKG.Connections("Connection 1")
        Dim ofd As New OpenFileDialog
        ofd.ShowDialog()
        oConn.DataSource = ofd.FileName
        oConn = Nothing

to:

Dim ofd As New OpenFileDialog
        ofd.ShowDialog()
        oPKG.Connections("Connection 1").DataSource = ofd.FileName

What is OpenFileDialog? Is it that DLL from the SQLDTS website?

[brendalisalowe]

It still isn't working. OpenFileDialog is a window where you pick the file you want. Have you not seen that before? Is that the best way to do it, or is there a better way?

[brendalisalowe]

What would this do:

oPKG.GlobalVariables("MyGlobalVariable").Value = txtGlobalVariable.Text

[vb_dba]

That just sets a Global variable in the DTS package.

Have you tried hardcoding the path/filename into the application instead of using the OpenFileDialog?

Something like
oPkg.Connections("Connection 1").DataSource = "C:\...."

[brendalisalowe]

I tried this:

oPKG.Connections.Item("Connection 1").DataSource = "C:\Accurint\Received\High_804.xls"

It still reads the old file that is stored in the package. Weird huh? Does ANYONE know how to change that?

[vb_dba]

I'm out of answers (or guesses, which ever you prefer).

[brendalisalowe]

So I've been reading some information on this crazy stuff. Do you know anything about ActiveX Script? I am thinking I need to setup that up for global variables, but I can't figure out how. Has anyone ever used that before?

[Granty]

Its very strange how it removes that SET because Im sure that's right......try turning code validation off and using SET?

You can do a ton with ActiveX scripts etc, DTS is pretty versatile. A colleague I used to work with did some really cool stuff but I just touched the bare bones myself Im afraid.

[brendalisalowe]

How do you turn off code validation? I'm still going crazy with this

[brendalisalowe]

I've still NEVER figured this out I don't know if I have really explained well of what I need to do. I need to be able to choose the Excel file that I want imported into SQL Server. Do I need to use Active Scripting when I create the package? How? I really would like to get this figured out. Any help would be GREAT! Thanks!

[TalonSoftware]

Thinking outsidethe box for a moment.......

Does the DTS need to be saved on the server?

Here's a thought. Open the DTS package, then File - Save As the package and save it as a VB Module. Create a VB project and add the module to it. You'll also need to set some of the DTS object references as well.

In the code, you could borwse and set the file location/name there and then run it, or you can save it. If you want to be able to pick the file and have it run immedialty, that would be ideal. If it's something that's scheduled to run later, then savign it back to the server will give it the new filename.