Answer My Searches

It turns out there’s a new MySQL connection library called MySQL Connector.

I installed it and then I just imported that into my script and everything else seemed to work normally. At least for my simply import script.

import mysql.connector as MySQLdb

I believe this library is designed for those who want to connect to MySQL on another server. It doesn’t seem to require any MySQL libraries to be installed.

When I tried to install mySQLdb it complained with the following message. I’m guessing the problem was that it requires MySQL to be installed:

File "/Users/pinerog/Downloads/MySQL-python-1.2.3/setup_posix.py", line 24, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found


I couldn’t find any documentation on this, so I wrote a quick Python script to test this:

"""
Test how many items can be in MySQL's in clause

Table I'm using for testing:
CREATE
    TABLE INTEST
    (
        id bigint NOT NULL,
        PRIMARY KEY (id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=latin1
"""
import MySQLdb

conn = MySQLdb.connect(host = 'localhost',
    user = 'root', passwd = '*****', db = 'scratch', charset='latin1')
curs = conn.cursor()

#test maximum sql in clause
def test_sql_in():
    test_up_to = 20000
    curs.execute('truncate table INTEST')
    for i in range(1,test_up_to):
        curs.execute('INSERT INTO INTEST (id) VALUES (%s)', (i,))
    conn.commit()
    for i in range(1,test_up_to):
        in_str = ','.join([str(v) for v in range(1,i+1)])
        select_sql = "select * from INTEST where id in (%s)" % in_str
        curs.execute(select_sql)
        rows = curs.fetchall()
        assert len(rows) == i
        if i % 100 == 0: print i

test_sql_in()

It ran fine. I tried it up to 20,000 items. Though it does get pretty slow, even with the index.

Note: I only tried this on MySQL 5.1.55 on Fedora 14.

Your homework:

• How does it run for other MySQL engines such as MyISAM?
• Can it handle a higher number like 1 million?

Here is a MS SQL Server (T-SQL) user defined function to split up a delmited string, and return the index value you care about.

Example:
select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
returns:
'def'

CREATE FUNCTION dbo.GetValueOfSplitStringByIndex (@sep char(1), @s varchar(1540), @index int)
RETURNS varchar(1540)
AS
-- Takes a @sep separated string, turns it into a list internally and returns the value from that
-- list found at the @index location.  Null is return if that @index does not exist.
-- Sample usage (testing):
-- select dbo.GetValueOfSplitStringByIndex('-','200-2505-0000-01-149-101-10',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',1)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',2)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',3)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l',4)
-- select dbo.GetValueOfSplitStringByIndex(',','abc,def,hhhhhhh,l,',0)
-- select dbo.GetValueOfSplitStringByIndex(',','abc',0)
Begin
	declare @pos int
	declare @piece varchar(500)
	declare @curr_index int
	set @curr_index = 0
	-- Need to tack a delimiter onto the end of the input string if one doesn't exist
	if right(rtrim(@s),1) <> @sep
	 set @s = rtrim(@s)  + @sep

	set @pos =  patindex('%' + @sep + '%' , @s)
	while @pos <> 0
	begin
		set @piece = left(@s, @pos - 1)
		-- You have a piece of data, so insert it, print it, do whatever you want to with it.
		if @curr_index=@index
		return cast(@piece as varchar(1540))

		set @s = stuff(@s, 1, @pos, '')
		set @pos =  patindex('%' + @sep + '%' , @s)
		set @curr_index = @curr_index + 1
	end
	return Null
end

So I set up a SQL Agent Job to run my SSIS package following these steps.

Here’s the crazy thing. The package would run perfectly when I ran it directly or via a batch file, but when I ran it through the SQL Agent Job it would die and I would see a message like this in the log file I set up:

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Connection 1" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.


I tried all of the work-arounds mentioned on this page. However none of them helped.

The answer it turns out is that 64 bit SQL Server Integration Services can’t handle Excel (xls) files! The trick is to use the 32 bit runtime of dtexec. To make the SQL Agent Job do this, go to the execution options tab of the job step and select “use 32 bit runtime”.

I tried making a flat file in SQL Server Integration Services but SSIS decided to put all of the output on one line. I couldn’t figure out a way to get it to make one line per record using a fixed width file type.

Finally what I ended up doing was creating a ragged right file, and making a dummy field with 0 width as the last field. You see, ragged right format works perfectly for exporting fixed width flat files with one record per line except that it trims the trailing spaces after that last field.

So making a dummy 0 width field lets you keep all of the trailing spaces in the last field you care about but still lets SSIS do its thing to add carriage returns/line breaks.

Source