Another BI & Programming Blog - Jason Yousef: June 2011

Thursday, June 30, 2011

SSIS to Sybase OLEDB connection error [IM002]. [Native Code: 30136]

Hi, I was getting that nasty error message, although you could test the OLEDB connection from the Sybase “data source configuration” and gets a success notification!!
[IM002]
[Native Code: 30136]
[ASEOLEDB]Data source not found and no default driver specified

I also made sure that the OLEDB connection are there and configured well under 32 BIT and 64BIT, and the Sybase “data source configuration” gives a success message when I test them.

So I thought the error is relevant to SSIS and it’s permuissions, not the Sybase, I started investigating the service account that SSIS is running under and if it got access to the C:\Sybase\DataAccess64\OLEDB\DataSources

As Sybase ASE 15.5 was installed on my root drive C:\ and I’m using win & 64 BIT.
I'd the same setup and configuration working on a vista 64 bit, but found other posts online about the driver and it is working well on windows 7.
The full error message is:

TITLE: Microsoft Visual Studio
------------------------------

Error at LookupMedFrequency [Connection manager "CareCast"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E19.
An OLE DB record is available. Source: "ASEOLEDB" Hresult: 0x80040E19 Description: "[IM002]
[Native Code: 30136]
[ASEOLEDB]Data source not found and no default driver specified".

Error at Data Flow Task [SSISERVisits [5801]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "CareCast" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
I also couldn’t find much about the error online, all what I found was couple of posts here :

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/ff5e2933-c29b-49b8-868c-cb4dca19b87d

and here:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/48695cc9-67e7-4ccb-8b5b-f64b7834ddc6

I even opened a new forum post:

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/1798b64e-e02a-4234-ac09-5cba38eaae29

Solution:
I found out that the 3 connections that I created was created only under “DataAccess64\OLEDB\DataSources\” and wasn’t created under the “DataAccess\OLEDB\DataSources\” So I just copied the 3 files and the problem solved!!!

Hope that helps someone one day ...

Sunday, June 26, 2011

SSIS....Articles that I refer to....

Package Configurations
http://msdn.microsoft.com/en-us/library/cc895212.aspx

Pass Variables from child to parent back
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/29/passing-a-value-back-and-forth-from-a-parent-package-to-a-child-package-in-ssis.aspx

Friday, June 24, 2011

FTP file and Folders list into a SQL Table

Today I came across a requirement to get all the files and folders list from a FTP server, The only way to get that will be through a script task that will loop through the files and get it into a SQL table.
Here's the question.
and here's my proposed solution....

I’ll be using a free test server ftp.secureftp-test.com User/Pass: test

We'll need to create a table to hold our results:

CREATE TABLE [dbo].[FTPList](
    [ListID] [int] IDENTITY(1,1) NOT NULL,
    [DateTime] [datetime] NOT NULL ,
    [Folder] [varchar](100) NOT NULL,
    [FileName] [varchar](100) NOT NULL)

Now let's start....

•    Drag a “Script Task”.

• 2-edit the code as shown here:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Text
Imports System.Net
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        'Create the connection to the ftp server
        Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
        Dim Folders As String()
        Dim Files As String()
        Dim FileName As String
        Dim Folder As String
        Dim Dir As String

        'Set the properties like username & password
        cm.Properties("ServerName").SetValue(cm, "ftp.secureftp-test.com") 'Serevr Name
        cm.Properties("ServerUserName").SetValue(cm, "test") 'user name
        cm.Properties("ServerPassword").SetValue(cm, "test") 'password

        Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))

        'Connects to the ftp server, to the root
        Dir = "/" 'Root directory
        ftp.Connect() 'Connect
        ftp.SetWorkingDirectory(Dir) 'Set working directory
        ftp.GetListing(Folders, Files) 'list fodlers and files

        Dim SQLStr As String    'SQL string to hold the root query
        Dim SQLStr1 As String   'SQL string to hold rest of the folders query
        Dim ConnString As String

        'SQL server connection to "Localhost" and my Database called "Admin"
        ConnString = "Data Source=localhost;Initial Catalog=Admin;Integrated Security=SSPI;"

        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = ConnString
        SQLConn.Open() 'open connection
        SQLCmd.Connection = SQLConn
        'write root file list
        If Not Files Is Nothing Then
            For Each FileName In Files
                'my table called "FTPList"
                SQLStr = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                SQLCmd.CommandText = SQLStr
                SQLCmd.ExecuteNonQuery()
            Next
        End If
        'write other folders and their files list
        If Not Folders Is Nothing Then
            For Each Folder In Folders
                Dir = "/" + Folder
                ftp.SetWorkingDirectory(Dir)
                ftp.GetListing(Folders, Files)

                If Not Files Is Nothing Then
                    For Each FileName In Files
                        SQLStr1 = "INSERT into FTPList(DateTime,Folder,FileName) VALUES ('" + Now() + "','" + Dir.Trim + "','" + FileName.Trim + "')"
                        SQLCmd.CommandText = SQLStr1
                        SQLCmd.ExecuteNonQuery()
                    Next
                End If

            Next
        End If
        ftp.Close()
        SQLConn.Close()
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

And the results will be at the FTPList table as shown:

That's it...Enjoy.......

Download the package from http://www.box.net/shared/77tqeitp99czj6feefxv

Thursday, June 23, 2011

How to enable GOD mode in win7 and Vista!

Today's post is short and sweet, I just couldn't resist to share this cool option !!Never heard about this trick before, so cool, gets you a better broad [control panel] over you PC!!

check it here http://www.bleepingcomputer.com/tutorials/tutorial158.html

Thursday, June 16, 2011

Ever wanted shapes and check boxes in SSRS?

I do some webdesigning in my own side business, and was installing some fonts, then this crazy idea came to my mind about using the default fonts that comes installed in windows to produce shapes in SSRS.

The various fonts are called " Webdings, Wingdings, Wingdings2 and Wingdings3 "

Basically you'll use IIF or SWITCH to set your desired shape, you'll need to use capital letters, small letter or numbers.

The list of the shapes and the corespondent key could be found here http://www.purplehell.com/riddletools/wingdingschart.htm

P.S. Use IE to view that page, firefox doesn't display that font correctly!!

Examples:

Hope that helps someone.....

Sunday, June 12, 2011

SSIS Error and Message Reference

This might be helpful to someone while troubleshooting your SSIS packages.
http://msdn.microsoft.com/en-us/library/ms345164.aspx

Saturday, June 11, 2011

Dynamically Iterate over a list of tables or views and export to flat files.

I recently came across an interesting Question In EE and was puzzled about how to achieve that using SSIS out of the box tasks, which was impossible as SSIS can;t use on the fly columns in the source or the destination.

SO definitely only could be done using a script task, but that wasn't really my favorite part, I'm the worst when it comes to that.

Anyway because I had some past experience as developing ASP pages using vb.net, so quickly came to my mind to build aDataGrid using a SQL query, then export the grid to a text file, which worked fine, The I came across the DataReader which is a neater and easier way.

So here I'm going to demonstrate how to Iterate over a list of views which has different structure to flat file using Script Task.

I'll be using the famous "AdventureWorksDW2008R2" DB for SQL 2008 and "AdventureWorks" for SQL 2005 which could be downloaded for free here, The demo is being done on a windows Vista 64BIT machine, with SQL and VS 2008 installed. but there shouldn't be any version different if you're using SQL 2005.

Alright enough talking....

For SQL server 2008 Users......

1-The first step is to identify what are the views that we be exporting.

Views.jpg (29 KB) (File Type Details)

Views

2-Check the columns count for each of them, just to illustrate the different in structure.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
SELECT A.name AS ViewName
  , count(B.TABLE_NAME) AS ColCount
FROM
 sys.objects AS A
 LEFT OUTER JOIN
  INFORMATION_SCHEMA.COLUMNS AS B
  ON A.name = B.TABLE_NAME
GROUP BY
 A.name
  , A.type
HAVING
 (A.type = 'v')
ORDER BY
 ColCount DESC

Toggle Highlighting Open in New Window Select All

3-I'll create 4 Variables, I love to always to the proper naming convention to tell the data type of the variable.

Variables.jpg (25 KB) (File Type Details)

Variables

A-Variable "ObjAllViews" will hold back the result set which has all the views' Names.
B-Variable "StrDelimiter" will hold the delimiter that you want to use between columns, I love to use the Vertical Line "|" because most of our data has commas in it, you can get it by pressing (SHIFT + \ )
C-Variable "StrViewName" will hold each individual view.
D-Variable "StrFilePath" will hold the desired path to save the files.

4-Create a connection to your server, in my case it's 'localhost' as I've my testing SQL server on the same machine, and I named the connection 'Localhost', take note of naming the connection because it's mentioned later in the script task.

oh...so now you get the idea? we will use the "Execute SQL Task" to get us the list of the views that exists in that table into "ObjAllViews" variable, then we will use "Foreach Loop Container" to shred resultset from "ObjAllViews" variable to each individual view name into "StrViewName" variable .

Then inside the "Foreach Loop Container" we will place a "Script Task" that will do the trick for us.

Now let's go over each tasks and how it's configured....

1-The control flow and the connection

CF.jpg (103 KB) (File Type Details)

ControlFlow and connection

2-Execute SQL Task

EST1.jpg (117 KB) (File Type Details)

EST1

EST2.jpg (81 KB) (File Type Details)

EST2

3-Foreach Loop Container

FEL1.jpg (112 KB) (File Type Details)

FEL1

FEL2.jpg (97 KB) (File Type Details)

FEL2

4-Script Task , Thanks for SIVA for helping me with the counter code.

ST1.jpg (89 KB) (File Type Details)

5- add This code

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()

        'Variable collection to hold the variables
        Dim VarCol As Variables = Nothing

        'Lock the 3 variables for read
        Dts.VariableDispenser.LockForRead("User::StrViewName")
        Dts.VariableDispenser.LockForRead("User::StrDelimiter")
        Dts.VariableDispenser.LockForRead("User::StrFilePath")


        'Fille the Variable collection
        Dts.VariableDispenser.GetVariables(VarCol)

        'Getting the variables' values, so we can use it later
        Dim ViewName As String = VarCol("User::StrViewName").Value.ToString()
        Dim delimiter As String = VarCol("User::StrDelimiter").Value.ToString()
        Dim FilePath As String = VarCol("User::StrFilePath").Value.ToString()


        'Construct the file name, example output: D:\vTargetMail.TXT
        Dim FName As String = FilePath & ViewName & ".TXT"

        'Build our Query
        Dim Query As String = "SELECT * FROM " & ViewName


        'Using StreamWriter
        Dim writer As StreamWriter = Nothing

        'Use our OLEDB COnnection
        Dim connection As OleDbConnection = New OleDbConnection(Dts.Connections("Localhost").ConnectionString)
        Dim command As OleDbCommand = Nothing
        Dim reader As OleDbDataReader = Nothing

        Try
            'Checking for the file, delete if exist(you can append or rename with [File.Move(FName, Today() & "_" & FName)]
            If File.Exists(FName) Then
                File.Delete(FName)
            End If
            'Open the OLEDB connection
            connection.Open()

            'Run the query
            command = New OleDbCommand(Query, connection)
            reader = command.ExecuteReader()

            If reader.HasRows Then

                'Stream Writer using the FNAME that we declared erlier
                writer = New System.IO.StreamWriter(FName)
                While reader.Read()

                    'Counter to get the columns number
                    Dim counter As Integer = 0
                    Dim fieldCount As Integer = reader.FieldCount - 1

                    While counter <= fieldCount
                        If counter <> fieldCount Then

                            writer.Write(reader(counter).ToString() & delimiter)
                        Else
                            writer.WriteLine(reader(counter).ToString())
                        End If
                        counter += 1
                    End While
                End While
            End If
        Catch ex As Exception
            Dts.Events.FireError(1, "", "Something Wrong happened!!!", "", 0)
        Finally
            connection.Close()
            writer.Close()
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Toggle Highlighting Open in New Window Select All

And The results .........

Files.jpg (17 KB) (File Type Details)

Files

For SQL 2005 users...

We need to change the script task, also the SQL query too as follow:

1- change the SQL query to

1:
2:
3:
4:
SELECT  (sys.schemas.name + '.' + sys.objects.name) AS ViewName
FROM    sys.objects 
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
Where type ='v'

Toggle Highlighting Open in New Window Select All

2- and the script task as shown here

script2005.jpg (86 KB) (File Type Details)

scripttask2005

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO

Public Class ScriptMain

    Public Sub Main()

        'Variable collection to hold the variables
        Dim VarCol As Variables = Nothing

        'Lock the 3 variables for read
        Dts.VariableDispenser.LockForRead("User::StrViewName")
        Dts.VariableDispenser.LockForRead("User::StrDelimiter")
        Dts.VariableDispenser.LockForRead("User::StrFilePath")

        'Fille the Variable collection
        Dts.VariableDispenser.GetVariables(VarCol)

        'Getting the variables' values, so we can use it later
        Dim ViewName As String = VarCol("User::StrViewName").Value.ToString()
        Dim delimiter As String = VarCol("User::StrDelimiter").Value.ToString()
        Dim FilePath As String = VarCol("User::StrFilePath").Value.ToString()

        'Construct the file name, example output: D:\vTargetMail.TXT
        Dim FName As String = FilePath & ViewName & ".TXT"

        'Using StreamWriter
        Dim writer As StreamWriter = Nothing

        'Try
        'Checking for the file, delete if exist(you can append or rename with [File.Move(FName, Today() & "_" & FName)]
        If File.Exists(FName) Then
            File.Delete(FName)
        End If

        'Open the OLEDB connection
        Dim connection As New OleDbConnection("Provider=SQLOLEDB;Data Source=Localhost;Initial Catalog=AdventureWorks;Integrated Security=SSPI;")
        connection.Open()
        Dim Query As String = "SELECT * FROM " & ViewName
      
        Dim command As OleDbCommand = New OleDbCommand(Query, connection)

        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then

            'Stream Writer using the FNAME that we declared erlier
            writer = New System.IO.StreamWriter(FName)
            While reader.Read()

                'Counter to get the columns number
                Dim counter As Integer = 0
                Dim fieldCount As Integer = reader.FieldCount - 1

                While counter <= fieldCount
                    If counter <> fieldCount Then

                        writer.Write(reader(counter).ToString() & delimiter)
                    Else
                        writer.WriteLine(reader(counter).ToString())
                    End If
                    counter += 1
                End While
            End While
            reader.Close()
        End If

        connection.Close()
        writer.Close()
        'Catch ex As Exception
        '    Dts.Events.FireError(1, "Nasty", "Something Wrong happened!!!", "", 0)
        '    Throw ex
        'Finally

        'End Try
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Toggle Highlighting Open in New Window Select All

Solution assumptions:

1-You're using SQL 2008 or SQL 2005.
2-Server name is "Localhost" hosted locally, otherwise, please change that.
3-You have write access to your D:\ Drive.
4-For SQL 2008 you have installed "AdventureWorksDW2008R2" DB and "AdventureWorks" for SQL 2005.

That's it...I hope my article helps someone to be more dynamic and flexible in SSIS and over come some of the Data Flow limitations.

Also I'm open to discussions and comments if you have a better way to do it.

The final package for SQL 2008 can be FOUND HERE and for SQL 2005 FROM HERE