Tag: SSIS 2005

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005: SSIS – Pushing Data to MySQL using Script Component Destination

Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration Services (SSIS), that ability was removed (in SQL 2008 SSIS there is an ADO.NET Destination that can update ODBC sources, so they brought some functionality back).

I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:

1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this http://dev.mysql.com/downloads/connector/odbc/5.1.html)

2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.

ms_01

3) Open the Script Component, set your input columns up, and then your Connection Manager

ms_02

4) Open the actual script, and you just have to add a few lines of code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

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

Public Class ScriptMain
    Inherits UserComponent

    Dim mySQLConn As OdbcConnection
    Dim sqlCmd As OdbcCommand

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection)
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn)
        sqlCmd.ExecuteNonQuery()

    End Sub

    Public Overrides Sub ReleaseConnections()
        Me.Connections.MySQLDatabase.ReleaseConnection(mySQLConn)
    End Sub
End Class

Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSIS – Two Ways Using Expressions Can Make Your Life Easier – Multi DB Select, Non Standard DB Select

In SQL Server Integration Services (SSIS), pretty much every task or transformation lets you set “expressions” up. Expressions are basically ways to set property values programmatically.

Here are two scenarios where you might use expressions (there are 100’s of uses, these are just two that are kind of related).

  1. Multiple Database Select – You have multiple databases – same schema, let’s say you have 300 installs of a 3rd party product and they all need their own database. I know it might sound impossible, but trust me, it can happen. Now, you want to run the same query over all databases, and pull data from a table, and dump into a data warehouse, for example. You could write 300 queries, and keep adding/removing based on the databases, you could create some elaborate dynamic SQL proc using loops, or you might have some other way, or, you could use SSIS Expressions.

    Now, how would you go about doing this? It is pretty easy actually. First step, you need to set up a loop in SSIS. You would want to grab a recordset of database names using an Execute SQL Task, or however you’d like, and store in an object variable. Then you can loop through that list. Your only difference in your query would be database name, so what you would do is have a variable for your SELECT statement. Name it whatever, but what you want to do is click on the variable, the properties of it. You will see Expression. Open the expression box and then set it to something like this

    ”SELECT Col1,Col2,Col3 FROM “ +  @[User:CurrentDatabaseName] + ".dbo.MyTable"

    image

    @[User:CurrentDatabaseName] is another variable to store the databasename that you would grab as you loop through your list of databasenames.

    Finally, in your dataflow, OLE DB source, you can change the Data Access Mode to “SQL Command From Variable”, and then it will let you choose your variable. As your for loop loops through your database names, and updates your SELECT variable, you can then select data from each database as you loop through them.

    image  

  2. Non-Standard Database Select – Not sure how to label this one, but here is what I am talking about. I like to make all my queries as stored procedures in SSIS, at least as much as possible. This works great when you are doing SQL Server to SQL Server, but what happens if its Oracle to SQL Server, DB2 to SQL Server, etc? Yes I know you can create stored procs on those systems, but you might be in a place or position where you just can’t or don’t want to. In that case you would want to use just standard T-SQL select statements to get data. You can easily put in params if the source is an OLE DB source, but what if it is an ODBC Source? You have to use the DataReader source, and you can’t easily set params – like a WHERE statement. You HAVE to use Expressions in order to have a query with a dynamic WHERE statement or passing in a variable as WHERE statement filter.

    So, throw a DataFlow on your package, and inside that, throw a DataReader source, and then set the connection to your ODBC Connection (ADO.NET Connection) and set the command text. Good to go. But where to set the connection? Not very intuitive. Go back to your DataFlow and look at the expressions for it. You will see one for DataReaderSource.CommandText (where DataReaderSource is the name of your DataReaderSource). You can set the expression up there. Now you can change an Oracle SQL Statement or DB2 or whatever to something that takes params without the need for a stored proc on that other database server.

So, while there are hundreds if not thousands of uses for expressions in SSIS, these are just a couple of uses that can make your life easier when trying to do more dynamic type queries in your DataFlow. Happy ETL’ing!

Categories
Business Intelligence Life

What Have I Been Up To? Windows 2008, Drive Backups, VS2008 Web Programming, SSIS Stuff

I have been pretty busy lately. I have some posts that I want to write up, but it seems they are getting bigger and bigger, longer. More stuff 🙂

Just a highlight, this weekend I decided to do a full drive backup and create a restore cd, I followed this process. http://www.darkchip.com/DriveImageXML/Complete%20System%20Recovery%20Process.html

I just didn’t stumble upon that. I did some research on drive backups, etc and found Drive Image XML. From there I did a backup to USB. But then I needed a way to restore, so I followed the BartPE tutorial for Drive Image XML and got that all set up. Just gives you a sense of confidence, knowing you have a full backup. Why did I decide to do this? Because I wanted to try Windows 2008 Server on my laptop, as a workstation, so next I did that.

Installed Windows 2008, set it all up. I am liking it, but there are some caveats so far. Wifi is off by default, you need to install some extra stuff. Turn on audio services, etc.

IE is totally locked down, but I installed Firefox right away anyways. Windows Live Writer won’t install, but yea you can get it to work – you need to get the MSI – http://on10.net/blogs/sarahintampa/20879/Default.aspx

The OS doesn’t find the iPhone, so I will have to figure that out. I haven’t installed the "Desktop Experience" yet, I want to try not installing it for as long as I can. Win2k8 flys with everything turned off by default.

Also have been working on some side projects, web site and other blogs/projects. Getting deep into VS2008 and .NET 3.5, which is fun. Ran into some debacles with the ProfileBase auto generation stuff, probably another post.

Also, so cool stuff in SSIS that I probably could write up, we will see.

I am all over the board right now, I know, but it is fun, doing Programming, Database, Web 2.0 blog stuff, System Admin, the whole gamut. I love it.