Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.Search for "SQLDMO .Net" in your favourite search engine and you will get a
ton of info.
Darrel
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET
> application. Does anyone know of any code (T-SQL, C# etc.) available to do
> this?
> Thanks.
>|||I was under the impression that SQLDMO was still only available as a COM
object ... thus in .Net, you would have to use a wrapper.
"Darrel Miller" <darrel@.tavis.ca> wrote in message
news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Search for "SQLDMO .Net" in your favourite search engine and you will get
> a ton of info.
> Darrel
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
>|||Earl wrote:
> I was under the impression that SQLDMO was still only available as a
> COM object ... thus in .Net, you would have to use a wrapper.
> "Darrel Miller" <darrel@.tavis.ca> wrote in message
> news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
That's true, but it's somewhat automated by Visual Studio .Net:
Visual Studio .NET generates an interop assembly containing metadata
when you add a reference to a given type library. If a primary interop
assembly is available, Visual Studio uses the existing assembly before
generating a new interop assembly.
To add a reference to a type library
1.. Install the COM DLL or EXE file on your computer, unless a Windows
Setup.exe performs the installation for you.
2.. From the Project menu, select References.
3.. Select the COM tab.
4.. Select the type library from the Available References list, or
browse for the TLB file.
5.. Click OK.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.
You probably need to wrap COM (SQLDMO). Here are some options:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:18qdnYXLU7Y_BqXeRVn-gA@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET application. Does anyone
> know of any code (T-SQL, C# etc.) available to do this?
> Thanks.
>
|||I used to use SQL-DMO, but when I built my scripts, the dependancies
were off. I found the following article, which takes this into
account:
http://www.sqlservercentral.com/colu...tionscript.asp
It's very fast as well.
Stu

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Scripter & foreign keys

Hey Peeps

I'm having trouble generating scripts for my databases with SMO. Any foreign keys in the base will blow up the code below. The error message says that the target collumn of the foreign key does not exist, which is hogwash. I have tried this on 3-4 different bases with exact same result. I'm 100 million % sure that these db's and foreign keys are ok.

I cant believe MS has relased something with so obvious a problem, so it must be my fault. So my questions are.

1) What's wrong with the code below?
2) Lets says for arguments sake that the problem reported was true, why would SMO even care about that? Im only asking it to script what it finds, not to argue about consistency etc. (I tried with the DriCheck option to false with same result)

/cheers
/Frederic

Server server = new Server("localhost");
Scripter scripter = new Scripter(server);
Database database = new Database(server, "Test");

database.Refresh();

int objectCount = database.Tables.Count;

SqlSmoObject[] objectsToScript = new SqlSmoObject[objectCount];

for( int t = 0; t < objectCount; t++ )
{
objectsToScript[t] = database.Tables[t];
}

scripter.Options.DriForeignKeys = true;

StringCollection output = scripter.Script(objectsToScript);

Hi Frederic,

In the above code, you are using the statement,

Database database = new Database(server, "Test");

this is actually used to create a new database. What you need to do is access an existing database on the server. Use the statement

Database database = server.Databases["Test"];

This should solve your problem.

Thanks,

Kuntal

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script to compare logins between 2 servers

Does anyone have the above in their code library? I need to compare logins between 2 servers in preparation to move a large number of DBs across.

Ideally, I am looking for duplicates.

In SQL Server 2000 you would query the syslogins table in the master database.

Code Snippet

USE master

GO

SELECT * FROM syslogins

In SQL Server 2005 you would use the new system information views. You can query the sys.syslogins view

Code Snippet

USE master

GO

SELECT * FROM sys.syslogins

Hope this helps get you on track. You can very easily write a comparison script and with a little work automate your findings.

NOTE: You can leave out the USE master - GO parts of the scripts for sql server 2005. the sys.syslogins view is available from all databases.

Script to change a connection manager

I am looking for code or assistance on how to modify an existing conneciton manger via a script component. I want to have a script change the column sizes for an existing connection manager. How can I get a specific connection manger to change its values.

I have looked at the connection manager, connections, etc classes but I am not certain how to do this as .NET is not my cup of tea.

Thanks.

Once set up though, you can't change column sizes because it will break any associated metadata in the package.|||

Not what I wanted to hear....

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

|||

1Dave wrote:

Not what I wanted to hear....

Well, SSIS can't just dynamically adjust itself depending on any changes to the source. Just not going to happen. Too many components rely on knowing the metadata in advance.

1Dave wrote:

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

Package configurations can change connection strings at run time. You can use an XML file, SQL Server based configurations, etc... You can also override connection strings on the command line, I believe. If you choose to store a connection string in a variable, that variable can be overwritten via the command line as well.|||

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

|||

1Dave wrote:

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

You could treat it as a variable column file and parse it manually inside your data flow with either script or a complex derived column. The connection manager would read the whole row as a single column.

Or, following your original path, you could use code to modify *another* package and execute it. Your original approach won't work because you can't modify the current package. You can modify another package and run that one against your file.

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?sql

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task scripts will not stop at breakpoints.

I can not debug any of my scripts. They execute just fine, but if I have a breakpoint set in the script code (in VSA), I get the message:

SQL server integration services script task has encountered a problem and needs to close. We are sorry for the inconvenience.

If you were in the middle of something, the information you were working on might be lost.

Debug Close

When I choose "Close" I get this:

sMicrosoft Visual Studio for Applications has lost the link to .

Your work will be exported to <<my Local Settings My Documents path>> when you quit the application.

OK

When I click "OK" my package executes normally, but no breakpoints will fire from that point on.

No need to post your message twice....

Script Tasks, not Script Components, correct?

One workaround is to use a MSGBOX().|||I was in the process of deleting my other post when you replied. I didn't want to post in a thread that had something marked as an answer. Yes, this is for script tasks. I certainly could use MsgBox() but that is hardly an answer to this problem. I could also write text out to a file, call a web service, insert a record into a table, etc. None of those things take the place of stepping through code. Is there a fix in the works for this issue? It really makes script tasks of limited use, or at least it makes them take much longer to develop.|||And you are following these steps?

http://technet.microsoft.com/en-us/library/ms140033.aspx|||With the exception of right-clicking (I was clicking in the margin to set the breakpoint), yes, that is exactly what I did.|||Do you have any SQL Server service packs applied?|||How would I tell?|||

JWardRogers wrote:

How would I tell?

In BIDS, go to Help->About and select SSIS. What is the version number reported there?|||9.00.1399.00|||That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.|||

Phil Brammer wrote:

That's the original.

Try grabbing the SQL Server Service Pack 2 and installing that. Many fixes are contained in SP1 and SP2 as well.

But unfortunately not the fix for my problem. :-(

SP2 is installed and it is still behaving the same way.

Version is now 9.00.3042.00

|||

I'm still having the same problem...

|||XP? Vista?|||

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

|||

JWardRogers wrote:

Server 2003 version 5.2.3790 (service pack 1.0)

Hot Fix: KB931836

4GB RAM

Do you also develop on a local workstation? Have you tried breakpoints on that if so?

Script Task Error?

I have the following code inside a Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections

Public Class ScriptMain

Public Sub Main()

Dim nm As String = "ship_metrics_rpt_*.csv"

Dim files As ObjectModel.ReadOnlyCollection(Of String) = My.Computer.FileSystem. _

GetFiles(CStr(Dts.Variables("MainPath").Value))

If files.Count > 0 Then

Dim dic As Generic.SortedDictionary(Of Date, String)

For a As Integer = 1 To files.Count

If files(a) Like nm Then

Dim nfo As System.IO.FileInfo = My.Computer.FileSystem.GetFileInfo(files(a))

dic.Add(nfo.CreationTime, files(a))

End If

Next

If dic.Count > 0 Then

Dim FinalFiles(dic.Count - 1) As String

Dim count As Integer

For Each kvp As Generic.KeyValuePair(Of Date, String) In dic

FinalFiles(count) = kvp.Value

Next

End If

End If

Dts.TaskResult = Dts.Results.Success

End Sub

The following exception gets thrown at "If files.Count > 0 Then"

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Why?

Thanks for your help

Has MainPath been passed in as a variable? It is scoped properly?|||

Yes it's been passed. You can insert "msgbox files.count" before "if files.count > 0" and get a count just fine. I don't understand it.

|||There are a number of problems here (easily fixed though)

1 . Fence post condition. The ReadOnlyCollection index starts at 0, not 1.
Change

For a As Integer = 1 To files.Count

To

For a As Integer = 0 To files.Count -1

2. Unitialized Variable:

Change

Dim dic As Generic.SortedDictionary(Of Date, String)

To

Dim dic As Generic.SortedDictionary(Of Date, String) = New Generic.SortedDictionary(Of Date, String)

|||Ah - I didn't catch the uninitialized part - that should fix it - thanks.

Script Task Error --Object reference not set to an instance of an object

I am trying to execute this code feom Script task while excuting its giving me error that "Object reference not set to an instance of an object." The assemblies Iam referening in this code are there in GAC. Any idea abt this.

Thanks,

PublicSub Main()

Dim remoteUri AsString

Dim fireAgain AsBoolean

Dim uriVarName AsString

Dim fileVarName AsString

Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection

Dim emptyBytes(0) AsByte

Dim SessionID AsString

Dim CusAuth As CustomAuth

Try

' Determine the correct variables to read for URI and filename

uriVarName = "vsReportUri"

fileVarName = "vsReportDownloadFilename"

' create SessionID for use with HD Custom authentication

CusAuth = New CustomAuth(ASCIIEncoding.ASCII.GetBytes(Dts.Variables("in_vsBatchKey").Value.ToString()))

Dts.Variables(uriVarName).Value = Dts.Variables(uriVarName).Value.ToString() + "&" + _

"BeginDate=" + Dts.Variables("in_vsBeginDate").Value.ToString() + "&" + _

"EndDate=" + Dts.Variables("in_vsEndDate").Value.ToString()

Dim request As HttpWebRequest = CType(WebRequest.Create(Dts.Variables(uriVarName).Value.ToString()), HttpWebRequest)

'Set credentials based on the credentials found in the variables

request.Credentials = New NetworkCredential(Dts.Variables("in_vsReportUsername").Value.ToString(), _

Dts.Variables("in_vsReportPassword").Value.ToString(), _

Dts.Variables("in_vsReportDomain").Value.ToString())

'Place the custom authentication session ID in a cookie called BatchSession

request.CookieContainer.Add(New Cookie("BatchSession", CusAuth.GenerateSession("EmailAlertingSSIS"), "/", Dts.Variables("in_vsReportDomain").Value.ToString()))

' Set some reasonable limits on resources used by this request

request.MaximumAutomaticRedirections = 4

request.MaximumResponseHeadersLength = 4

' Prepare to download, write messages indicating download start

Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", _

Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), String.Empty, 0, fireAgain)

Dts.Log(String.Format("Downloading '{0}' from '{1}'", Dts.Variables(fileVarName).Value.ToString(), Dts.Variables(uriVarName).Value.ToString()), 0, emptyBytes)

' Download data

Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

' Get the stream associated with the response.

Dim receiveStream As Stream = response.GetResponseStream()

' Pipes the stream to a higher level stream reader with the required encoding format.

Dim readStream AsNew StreamReader(receiveStream, Encoding.UTF8)

Dim fileStream AsNew StreamWriter(Dts.Variables(fileVarName).Value.ToString())

fileStream.Write(readStream.ReadToEnd())

fileStream.Flush()

fileStream.Close()

readStream.Close()

fileStream.Dispose()

readStream.Dispose()

'Download the file and report success

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

' post the error message we got back.

Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

EndTry

EndSub

It isn't clear what is failing, a line number or similar would help. Since you mention external assemblies, have you read this -

Referencing Other Assemblies in Scripting Solutions
(http://msdn2.microsoft.com/en-us/library/9b655bcd-19f6-43d8-9f89-1b4d299c6380.aspx)

Script Task and Variable Type

Hi,

I am using the following code in Script Task and it is giving me the error as shown below

FileCount is a variable defined as Int16 (with initial value = 0 ) and it is part of Read/Write variables portion of Script

Dim FileCount As Int16

FileCount = CType(Dts.Variables("FileCount").Value, Int16)

Dts.Variables("FileCount").Value = FileCount + 1 //Error on this Line

The type of value being assigned to @.User::FileCount differs from the current variable type. Variables may not change during execution.

Please Guide what is wrong with this.

The result produced from "FileCount + 1" is of type Integer, you are trying to assign it to type Short. Change the line to the following and it should work:

Dts.Variables("FileCount").Value = CShort(FileCount + 1)

Script Out T-SQL Code Programmatically

I'm looking for a way to programmatically (ideally using T-SQL) script
out code objects in order of precedence .
I must make clear that I'm quite satisfied that I have found a way of
listing the names Views, Stored Procs & Functions that need to be
changed in order of precedence (eg if SP spEmployeeUpdate depends on
view vselEmployee which in turn depends on table tblEmployee then if
tblEmployee has been updated you need to recompile (?) vselEmployee
then spEmployeeUpdate). OK, so that's what this post is not about!
So . . . I have a temporary T-SQL table which comprises in order the
code objects which need to be recompiled - all well and good but how
can I script out the objects so I can run the script once and it's
done?
Using the above example, after tblEmployee has been updated I would
like to generate code like this . . .
ALTER VIEW vselEmployee
AS
BEGIN
|
|
END
GO
ALTER PROCEDURE spEmployeeUpdate
AS
BEGIN
|
|
END
GO
I've tried using SysComments.Text (which contains the code) but without
success (truncation of text, loss of formatting etc).
I know that Enterprise Manager allows me to script out a selection of
code objects but 1) I can't control it programmatically; 2) it's not in
any useful order; 3) It uses "DROP" & "CREATE" rather than the
preferred "ALTER" etc.
I've seen several posts on this topic but they tend to get bogged down
on the bit I've already solved.
Any ideas?Hi
You may want to look at using DMO to do this, similar to
http://www.nigelrivett.net/DMOScriptAllDatabases.html
John
"Pete Nurse" wrote:

> I'm looking for a way to programmatically (ideally using T-SQL) script
> out code objects in order of precedence .
> I must make clear that I'm quite satisfied that I have found a way of
> listing the names Views, Stored Procs & Functions that need to be
> changed in order of precedence (eg if SP spEmployeeUpdate depends on
> view vselEmployee which in turn depends on table tblEmployee then if
> tblEmployee has been updated you need to recompile (?) vselEmployee
> then spEmployeeUpdate). OK, so that's what this post is not about!
> So . . . I have a temporary T-SQL table which comprises in order the
> code objects which need to be recompiled - all well and good but how
> can I script out the objects so I can run the script once and it's
> done?
> Using the above example, after tblEmployee has been updated I would
> like to generate code like this . . .
> ALTER VIEW vselEmployee
> AS
> BEGIN
> |
> |
> END
> GO
> ALTER PROCEDURE spEmployeeUpdate
> AS
> BEGIN
> |
> |
> END
> GO
> I've tried using SysComments.Text (which contains the code) but without
> success (truncation of text, loss of formatting etc).
> I know that Enterprise Manager allows me to script out a selection of
> code objects but 1) I can't control it programmatically; 2) it's not in
> any useful order; 3) It uses "DROP" & "CREATE" rather than the
> preferred "ALTER" etc.
> I've seen several posts on this topic but they tend to get bogged down
> on the bit I've already solved.
> Any ideas?
>|||Thanks John, that's excellent code - very clear and well written. I'm
busy now trying to work out what it's doing!

Script for Publishing

Can the script files be written in any .net language? Writing code in VB
makes me ill.You could write a C# custom app using SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"?" <?@.discussions.microsoft.com> wrote in message
news:A37994BD-2958-457F-9C93-295B93406201@.microsoft.com...
> Can the script files be written in any .net language? Writing code in VB
> makes me ill.

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.

To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component to send email

Is there a way I can on any exception, call my .NET code using System.Mail in SSIS 2005 and use my custom code to send emails rather than the Mail component? The mail component sucks, you can't change the look & feel of the email being sent using CSS but if I can fire off my own code to send the email, then that would work great.

To ensure your script task fails on any error, you would create an On Error event handler and add the task in there.

The script task can use just about any .Net component but the system stuff is there already. Try System.Net.Mail for a start and see if that does what you want. If not you may have to get a third-party component, then just add a reference to it. For new components you will need to place them in the framework folder to be able to add a reference e.g. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\

|||So I would just do an OnError then a script component to call my .Net email function? Then just pass through the System variables to the script variable? cool.|||Yes, event handlers are great for this. No more messing about with workflow constraints for this type of package (container) wide handling.