Creating Connection Manager on the fly.

Question

I have a script task.
Inside - I am creating a Connection Manager on the fly (Reason - my http connection - url will be created on the fly also).
The task is to download xml file from url with credentials.

How do I specify credentials and why is it giving me the error?
Where do I specify user name and password for this url (xml file location)?

SSIS package "TEst.dtsx" starting.
Error: 0xC0016001 at TEst, Connection manager "URL HTTP Connection Manager": The URL specified is not valid. This can happen when the server or proxy URL is null, or in an incorrect format. A valid URL format is in the form of http://ServerName:Port/ResourcePath or https://ServerName:Port/ResourcePath.
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Exception from HRESULT: 0xC0016001 ---> System.Runtime.InteropServices.COMException (0xC0016001): Exception from HRESULT: 0xC0016001
   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSHttpClientConnection100.DownloadFile(String FileName, Boolean vbOverwriteDestination)
   at Microsoft.SqlServer.Dts.Runtime.HttpClientConnection.DownloadFile(String fileName, Boolean OverwriteDestination)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Dts.Runtime.HttpClientConnection.DownloadFile(String fileName, Boolean OverwriteDestination)
   at ST_32c52a80fc384140ab268f17264bf328.vbproj.ScriptMain.Main()
   --- End of inner exception stack trace ---


This is what I have:

 

''Create connection
ConManager = Dts.Connections.Add("http")
ConManager.Name = "URL HTTP Connection Manager"
''create connection string
strUrl = http://test.test.com/webcasts/1.xml
' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
nativeObject = Dts.Connections("URL HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
' Download the file
' Save the file from the connection manager to the local path specified
Dim filename As String = "C:\Test\1.xml"
connection.DownloadFile(filename, True)
' Confirm file is there
If File.Exists(filename) Then
MessageBox.Show(String.Format("File {0} has been downloaded.", filename))
End If

---

V. A.

Answer 1

Where does this code exist? Is it in a Script Task? You can't create a Connection Manager from within an executing package in that same package, nor would you want to. Just create an http Connection Manager at design-time and set its ConnectionString property at execution-time - thre is no need to create the Connection Manager at execution-time.

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 2

If anyone looking for the code.

It is working.

                'Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
                Dts.Connections("HTTP Connection Manager").ConnectionString = strUrl
                nativeObject = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)

                'Create a new HTTP client connection
                Dim connection As New HttpClientConnection(nativeObject)

                ' Download the file #1
                ' Save the file from the connection manager to the local path specified

                Dim filename As String = "C:\Test\Test.xml"
                connection.DownloadFile(filename, True)

                ' Confirm file is there
                If File.Exists(filename) Then
                    MessageBox.Show(String.Format("File {0} has been downloaded.", filename))
                End If

---

V. A.

Answer 3

Niec work. Although it might have been a bit easier to use expressions :)

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet

Answer 4

And a File Properties Task to check for the file existence.

---

Answer 5

I also continue this task (my task 2).
I needed to loop through each xml file and insert data to DB.

This is how I did it. Correct me if I am wrong.

Solution:
1. I created ForEach Loop Container in Control flow.
a. Collection - Foreach File Enumerator
b. Collection - specified the path to the folder where to look.
c. Collection - specified *.xml
d. Variable Mapping - FileName (which I created before as a string with Test.xml (this xml exists - development file)
2. Create a data flow inside of ForEach Loop container.
3. Data Flow has XML Source and OLE DB Destination
4. XML Source - point to "XML FILE from variable" and point to development xml file (create xsd also).
5. Create OLE DB Destination
6. Run it.

---

V. A.

Answer 6

Continue to my task 2 (below)
One more thing I need to do.
Get file name to one of the column in Db (at the same time with other data from inside of xml file).
Any idea?

---

V. A.

Answer 7

Use the Derived Column transformation.  In the upper left hand corner of the Derived Column transformation editor, open the "variables" node.  You'll see your filename variable.  Drag that into the first row in the "expression" column.  Press OK.

---

Answer 8

Thank you very much!
I appreciate your help.
I actually though of using it too.

Thank you!

---

V. A.