Migrating DTS Active X/ADO script to SSIS

Hi All,

In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:

Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop

I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.

Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?

Thanks in advance
Jeff

A script task would be one way to do this. You could use an ADO.Net connection in the Script. You can then use that to help manager the connection information, and it will return a SqlConnection for use in the script. For example-

Public Sub Main()

Dim connection As SqlClient.SqlConnection = CType(Dts.Connections("MyAdoSqlClient").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

Dim command As New SqlClient.SqlCommand("EXEC ProcName", connection)

command.ExecuteNonQuery()

Dts.Connections("MyAdoSqlClient").ReleaseConnection(connection)

Dts.TaskResult = Dts.Results.Success

End Sub

A script component is only used inside the data flow, and this doesn't seem like it is moving data anywhere really. You could use a data flow, OLE-DB Source to Recordset Destination, but then what. I think this fits into the over engineered category as does the option below.

Alternative methods could be to use the Execute SQL task to get the recordset and the For Each Loop to process the recordset. I'd be tempted by this option. I'd use a script task maybe to just build teh SQL statement inside the loop, then store it in a variable. I don't quite get the Recordet2 and Update steps, but maybe an Execute SQL Task would do this. If you don't end up with any real worker tasks inside the For Each Loop, then I would just do it all in a Script Task again, but using VB.Net and the nice things it gives over ActiveX Script.

migrate users

How can I script out existing SQL users from a user database on Production s
erver to the same user databse migrated to Test server ? (Both srv are on w2
k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all
the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed
the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls helpHi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help

migrate users

How can I script out existing SQL users from a user database on Production server to the same user databse migrated to Test server ? (Both srv are on w2k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls help
Hi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help
sql

migrate users

How can I script out existing SQL users from a user database on Production server to the same user databse migrated to Test server ? (Both srv are on w2k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls helpHi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
--
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help

Migrate or Script Subscriptions and Schedules

I've used RSScripter.exe to move reports and permissions to a new server.
The scripter does not seem to be bringing over subscriptions or shared
schedules.
Is there a good way to migrate subscriptions for existing reports from one
server to the other?It seems the log shows the following error: EXECUTE permission denied on
object 'xp_sqlagent_is_starting'
I've tried running this on the box, under the context of local
administrator, even the domain account running the ReportServer service and
it's still not working.
"Scott" wrote:
> I've used RSScripter.exe to move reports and permissions to a new server.
> The scripter does not seem to be bringing over subscriptions or shared
> schedules.
> Is there a good way to migrate subscriptions for existing reports from one
> server to the other?

Microsoft.SqlServer.Management

I am looking for a way to script out all tables within a SQL Server 2005 database using VB.NET. I am writing a backup utility and have been able to do stored procedures, the data within the tables, but am now stuck on scripting out the tables with their primary keys, indexes, etc. Sure, I could hard-code some stuff like "CREATE TABLE dbo" & strTable, but I am pretty sure there is a way to do it within VB.NET using SMO. So, any help that someone could offer is greatly appreciated.

Thanks

I just googled it and found the following:

http://www.yukonxml.com/articles/smo/

Not sure if the above will be any help for you

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer column ordinal from name?

Hi,

I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper.

My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me?

Dave

PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.

To read and write buffer columns by ordinal position in a script transform, load a name => index dictionary in the PreExecute function. The dictionary key would be the column name, and the dictionary value a structure containing column metadata, including the buffer column index you're referring to, and whatever else you'd like.

In PreExecute() function, load up the dictionary. It will then be available for use when you require direct column access by ordinal position. Here's an example which uses the GetColumnIndexes in lieu of BufferManager.FindColumnByLineageID().

Imports System

Imports System.Data

Imports System.Math

Imports System.Collections.Generic

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

Private cols As Dictionary(Of String, ColumnInfo) = New Dictionary(Of String, ColumnInfo)

Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If cols.TryGetValue("GeneratedStr_1", currentColumnInfo) Then

' retrieve column metatdata by column name

inputBuffer.SetString(currentColumnInfo.colIndex, Guid.NewGuid().ToString())

End If

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

End Sub

Public Overrides Sub PreExecute()

BuildColumnDictionary()

End Sub

Private Sub BuildColumnDictionary()

Dim indexes() As Integer

Dim input As IDTSInput90

Dim col As IDTSInputColumn90

Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)

'presumes GetColumnIndexes order matches iterator order

'as BufferManager is not available to my knowledge in ScriptComponent

indexes = Me.GetColumnIndexes(input.ID)

For Each col In input.InputColumnCollection

Dim columnStructure As New ColumnInfo

With columnStructure

.colName = col.Name

.colLength = col.Length

.colIndex = indexes(offset)

'Normally, BufferManager would be used, but its not exposed in Script Component

.colPrecision = col.Precision

.colScale = col.Scale

.colType = col.DataType

End With

Me.Log(String.Format("Name {0} Buffer Index {1} offset {2} ", col.Name, indexes(offset), offset), 0, Nothing)

cols.Add(col.Name, columnStructure)

offset += 1

Next

End Sub

Public Structure ColumnInfo

Dim colName As String

Dim colType As DataType

Dim colIndex As Int32

Dim colLength As Int32

Dim colPrecision As Int32

Dim colScale As Int32

End Structure

End Class|||

Hi Jaegd

Thanks for the code, capturing the PipelineBuffer in ProcessInputs for later use in _ProcessInputRow is a good idea. I'd also not noticed that PipelineBuffer has Get{Type} \ Set{Type} methods.

Your assumption that the 'GetColumnIndexes order matches iterator order' does not appear to hold though (at least for the Excel Data Source) but I found an alternative that appears to be ok

With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = input.InputColumnCollection.GetObjectIndexByID(col.ID)

.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With

Dave

Microsoft.SqlServer.Dts.Pipeline.BlobColumn

I am using Component Script to do - Transforming Comma-delimited list row data to column

and I want to use MessageBox to see the value

Dim DataPnts As String

DataPnts = Row.DataPnts.ToString() -- this is my input column (data type = text in Source table and I put as Unicode string [DT_WSTR] in Output column)

MessageBox.Show(DataPnts, "DataPoints1", MessageBoxButtons.OK)

and why can't I see it. It gives me some message with Microsoft.SqlServer.Dts.Pipeline.BlobColumn. Why?

Values = DataPnts.Split(CChar(","))

Please point me to more info on how to do transform Comma-delimited list row data to column.

Thanks.

Vita wrote:

and why can't I see it. It gives me some message with Microsoft.SqlServer.Dts.Pipeline.BlobColumn. Why?

It would be very valuable if you posted here the full message you're receiving.

|||

The only thing I see in the message box is - "Microsoft.SqlServer.Dts.Pipeline.BlobColumn" - exact words.

And that is it. No value at all.

And if I do

Values = Row.DataPntsConv.Split(CChar(",")) - it shows me the error that Split is not a member of Microsoft.SqlServer.Dts.Pipeline.BlobColumn.

This is why I created a Variable as String and tried to do a Split on it.

I think the problem with text data type I have in the Source table for this field.

|||

Look at the type of the object, it is not a string type or something that supports casting top a string. It is a complex type, a Microsoft.SqlServer.Dts.Pipeline.BlobColumn class actually.

Take a closer look at the methods on Row.DataPnts, I think there should be so read and write methods. Use them to get the data you want. Just bear in mind that this is a big texttype, not a string, and you don't want it leaving up to 2GB of data in a string for you!

|||

Ok, I must be extra-dense today. But the light bulb just came on.

The problem is that the Microsoft.SqlServer.Dts.Pipeline.BlobColumn class inherets its implementation of the ToString method from the Object base class. This means that it just returns the name of the class: "Microsoft.SqlServer.Dts.Pipeline.BlobColumn." As you can probably imagine, this would handily explain the MessageBox message you've reported.

So... You actually need to use the BlobColumn.GetBlobData method instead: http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.blobcolumn.getblobdata.aspx

Enjoy!

|||

Thanks!

Can you give the example of it?

How to use it in

DataPnts = Row.PointsValue.ToString()

Values = DataPnts.Split(CChar(",")

May be I am missing some references to use it, but if I put GetBlobData - the code shows error.

I use already

Namespace: Microsoft.SqlServer.Dts.Pipeline
Assembly: Microsoft.SqlServer.PipelineHost (in microsoft.sqlserver.pipelinehost.dll)

Or may be I should put something before the word GetBlobData. Like buffer or something like it.

Thanks.

|||

I do not have an example to share. As Darren pointed out, this BLOB colum can be very large, so it's non-trivial to handle all cases, as depending on the size of the data in each row you may be able to get it all in one pass, or you may need to batch it into many smaller passes.

Basically you need to declare a variable of type Byte array, and then assign that value with the value returned from the call to Row.PointsValue.GetBlobData(). I can't really get more specific than this without writing the code myself...

|||How do I do a split on BlobData?|||You can't do it directly - you have to get the blob into a string first.

Microsoft Visual Sutdio for Applications has lost the link to .

Hello,

I am struggling with a really ugly problem in SSIS.

I am designing SSIS package where I have a couple of Script tasks. Let's call them Script Task 1 and Script Task 2. Everything was working fine until this morning I added another Script taks (Script Task 3), which was trying to do some rudementary file manipulation. At that point I started getting this message:

"Microsoft Visual Sutdio for Applications has lost the link to .

Your work will be exported to c:\documents and settings\".

This is it! "lost a link to "dot". After this, the VS environment continues to run in debug mode, so it doesn't even crash stirctly speaking. I can stop debugging and continue developing, but nothing would run anymore and I would keep getting the same error. Moreover, after removing Script Task 3 (which seems to be the culprit), I would still keep getting the same error! Only removing all the script tasks completely and then adding them back gets the package to run again.

Any ideas?

Hi Michael,

This is the first time we are seeing this problem. The only thing that I can think that happened is that somehow those scripts got in a bad state.

One way to fix this kind of errors is to try and setting the precompiled property to false on those script tasks, remove all the breakpoints from the scripts, save the package and then reset the precompiled to the desired value and re-add the breakpoints. By doing that the precompiled version of the scripts will be refreshed and hopefully everything will be back in sync.

Also make sure you have SQL Server SP2 installed because it fixes some problems with scripting that might cause these errors.

Another problem might be if you generated script 3 using copy/paste in the package designer from one of script 1 or 2. Some problems regarding that operation were fixed in SP2.

Let me know if that helped.

Silviu Guea [MSFT] SQL Server Integration Services