Error running parameterized query against MySQL database in SSIS package

I am building a SQL 2005 SSIS package to accomplish a task that was previously done in SQL 2008 without any problems. The first control flow step connects to a SQL 2008 database and retrieves and stores an data in variables. That part isn't a problem. The second step connects to a MySQL database and runs a query using the varaibles as input parameters. At this Point I get the error:

'INSERT INTO test (TestID) VALUES (?test)': 'Only MySqlParameter objects may be stored'

I try both with @ or ?. An also  the "Allow user parameters" and "Use old syntax" in the my sql connection settings. Nothing helps.

If I try it with
'INSERT INTO test (TestID) VALUES (1) it works also fine. Only with parameters it fails.

On my SQL 2008 SSIS packets it works fine. I use my sql ado.net connector 6.1.3 on both SQL 2005 and SQL 2008.

ANyone an idea?

Thans a lot.
Booster0110Asked:
Who is Participating?
 
Reza RadConnect With a Mentor Commented:
now i checked whole work with mysql ODBC connector instead of ado.net connector
that work correctly with SSIS 2005.
first download it from :
http://dev.mysql.com/downloads/connector/odbc/5.1.html

then install it.
after installation try to create a ODBC connection to mysql as below in attachement images
remember you need to use this connection string:
DRIVER={MySQL ODBC 5.1 Driver};SERVER=<host>;DATABASE=mydb;UID=root

you can see how to set it in first image

then try again



111.jpg
222.jpg
333.jpg
0
 
Reza RadCommented:
you can use ? as parameter in OLEDB connections
if you use another connection type , you must specifiy parameters with other types
maybe you need to use this naming : @yourparametername

It's better to say what is your connection type?
0
 
Booster0110Author Commented:
Hi Reza Rad,

thanks for your reply. I use an ado.net connection type. On this type I must use @, but I also try ?. Its always the same error;
'Only MySqlParameter objects may be stored'

On my SQL 2008 SSIS project all tasks work fine. Unfortunalty I need the hole projerct as an SSIS 2005.

Thanks again for your support.
Christian



0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Reza RadCommented:
I test it on ssis 2005
but on sql server not mysql ( but i think you can do this with mysql too)
connection type was ADO.NET
I attached images
you need to use @yourparametername ,look at attachments carefully.

this link list detail parameter namings for each connection types in SSIS:
http://technet.microsoft.com/en-us/library/ms140355.aspx
111.jpg
222.jpg
0
 
Booster0110Author Commented:
Hi again,

we misunderstood us. ;)

I got my data from a mssql table, put it into a ado recordset (everthing in a flow control). Then I put on a foreach control within I iterate over the ado recordset (the one with the data from the mssql table). On each iteration I want to insert a new row into a my sql database. So I connect to it (this step works fine) and then I build up a query like this one:

INSERT INTO Test (Test)
VALUES (@test)

Where @test is my parameter. Then I go to the parameter tab and defined the declare parameter.
After this I start the project and get the error:

'Only MySqlParameter objects may be stored'

To connect to the my SQL db I use the mysql connector in version 6.1.3 or 6.2.2.

Thats the hole problem and I can't handle it.

Any idea?

Thanks in advance.
0
 
Reza RadCommented:
OK, but this must be same at all,because problem is in parameter mapping.
could you upload your .dtsx package here?
right click on your package file and select view code
then copy xml code behind in a text file and upload it here
0
 
Booster0110Author Commented:
Hi again,

as an attchment I add a testjob.dtsx and 2 scripts to create the based tables you need for the job. One script is fot mssql the other for mysql. My work based on the mysql connector 6.2.2

Link:
http://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-6.2.2.zip/from/http://artfiles.org/mysql/

The mySQL DB Version is: 5.1.37
The MSSQL DB Version is 2005 incl all SPs

Hopefully you have an idea to help me out :)

Thanks a lot
0
 
Booster0110Author Commented:
here the attchemnt. Please rename the TestJob.txt to TestJob.dtsx.

TestJob.Zip
0
 
Reza RadCommented:
OK, i spend about 1.5 hour on checking the problem
It's really confusing , but I think mysql ado.net connector has a problem with SSIS 2005.
because I could connect to mysql with mysql ado.net connector . but i couldn't run any query on mysql database. so I think these two have conflict with each other.
Then i tried this scenario on SSIS 2008, and anything was fine.
I create new package exactly like yours in 2005 , and everything was fine, data imported successfully, as you said in your previous posts.
I hadn't any idea about why SSIS 2005 have this issue with mysql ado.net connector , but I think this problem really exists , maybe it's a bug.
0
 
Booster0110Author Commented:
Hi again,

thanks alot for your time invest. :-)

Great work. My favorite is an ado.net connection but sometimes its better to handle it on the "old way". On my mind its a bug in the SSIS 2005 module. MS has rewritten it from DTS but forgot something. In SSIS 2008 they correct it or what else.

Bit nice that this stuff works y using your idea.

Thanks a lot and the furture will show if i can help you the next time :)

Have a nice week.
0
 
Reza RadCommented:
Glad to help,
Regards,

0
 
Booster0110Author Commented:
Hi again,

I have a last question.

I'll get an error on trying to insert a dattime variable into a mysql timestamp/datetime field.
On ODBC I can select SQL_Date / SQL_TIIME or SQL_Timestamp but nothing off this data types are working to map the mssql datetime field during my sql query to insert a row into the my sql database.

Not supported data type in parameter mapping '3'

Regards
0
 
Reza RadCommented:
you can fetch datetime field from ms-sql to a string field ( do it in your select query with convert(varchar(max),datetimefield)  )
and then use appropriate string type for parameter in ODBC
then use this parameter with a UNIX_TIMESTAMP in your insert query like below
insert into mytable(mytimestampfield) values(UNIX_TIMESTAMP(@stringparameter))

0
 
Booster0110Author Commented:
its not working. I convert the mssql date to string with convert(varchar(max), StartTime, 120). On style reaseon I must set the syste option. The result on every datetime is for example 2009-01-11 13:00:00. Thats what the funtion UNIX_TIMESTAMP(?) expect.

But unfortunaltly I'll get the same error;
Not supported data type in parameter mapping '3'

The parameter is now String. I change the select statment for the source rows and change the Insert statment for the destination (mysql). I also try TIMESTAMP(?).

An idea?

0
 
Reza RadCommented:
could you paste whole your insert statement here?
0
 
Booster0110Author Commented:

INSERT INTO tbadvancedtripdata (idHW, SmartcardNumber, timeStart, WorkState, SendRetries, kmBegin, kmEnd, timeEnd, LatitudeStart, LongitudeStart,
		        	        LatitudeEnd, LongitudeEnd, CountryStart, CountryEnd, StreetStart, StreetEnd, ZIPStart, ZIPEnd, CityStart, CityEnd,
			        TripState, timeStartBias, timeEndBias, CompanyID, CategoryID)

VALUES			        (?, ?, UNIX_TIMESTAMP(?), ?, ?, ?, ?, UNIX_TIMESTAMP(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Open in new window

0
All Courses

From novice to tech pro — start learning today.