Need to create csv file from script output on mysql database

Question

Post reply

Need to create csv file from script output on mysql database

regenna williams

SSC Journeyman

Points: 89
More actions
February 19, 2007 at 9:25 am

#68276

Please help me. I'm don't have that much sql experience but I was able to write the scripts below to extract the data i need. Now I need a job that is scheduled to run daily to take the scripts below, convert the data into a csv flat file format and put the file on a network share. I have been really unsuccessful in getting anyone in my company to assist because it's coming from a mysql database. Everyone keeps telling me this is easy. Please help me. I have a project on hold because of this......
/*Shows card activity for all sox doors ie...past admin and break areas updated 2/12/07* and need to run at 11:59:00 daily*/
Select DISTINCT lastname,firstname, cards.cardid, cards.usertext14, cards.usertext10,
events.cardholder, events.eventdescription, events.eventLocation, events.eventdate
from Cards, accesslevels, events , cardAccessLevels
where cards.cardid = cardaccesslevels.cardid
and Cards.Cardnumber = Events.cardnumber
and cards.expiredate > getdate()
and events.EventDate > CONVERT(DateTime, convert(varchar(20),GETDATE(),101))
and (CardAccessLevels.AccessLevelID = '0' /*Telco All */
or CardAccessLevels.AccessLevelID = '5' /* ECC */
or CardAccessLevels.AccessLevelID = '7' /* Telco MDF – BellSouth Side */
or CardAccessLevels.AccessLevelID = '12' /* Media Storage Room */
or CardAccessLevels.AccessLevelID ='13' /* Receiving to Chiller Room */
or CardAccessLevels.AccessLevelID ='15' /* Service Yard to Generator Room */
or CardAccessLevels.AccessLevelID = '17' /* Door 121 ECC to RF */
or CardAccessLevels.AccessLevelID = '18' /* Door 122B War Room to RF */
or CardAccessLevels.AccessLevelID = '20' /* IT Lab Core Management */
or CardAccessLevels.AccessLevelID ='21' /* Prod RF from Vestibule */
or CardAccessLevels.AccessLevelID = '22' /* Prod RF North */
or CardAccessLevels.AccessLevelID = '23' /* IT Lab */
or CardAccessLevels.AccessLevelID = '25' /* Production Raised Floor */
or CardAccessLevels.AccessLevelID = '26' /* Battery Rooms */
or CardAccessLevels.AccessLevelID = '27' /* UPS Rooms */)
order by lastname

/*THIS SHOWS CURRENT ACCESS AND IS WHAT IS USED FOR CURRENT AUDIT WITH LAST TIME USED*/
Select DISTINCT lastname,firstname, cards.cardid, cards.usertext14,
usertext10,cardinfo.lastTimeUsed,cardinfo.lastActivity
from Cards, cardaccesslevels, readers,cardinfo
where cards.cardid = cardaccesslevels.cardid
and cards.expiredate > getdate ()
and cards.cardID = cardInfo.cardid
and (CardAccessLevels.AccessLevelID = '0' /*Telco All */
or CardAccessLevels.AccessLevelID = '1' /* Master */
or CardAccessLevels.AccessLevelID = '2' /* Leadership Group Under Greg Satusky */
or CardAccessLevels.AccessLevelID = '5' /* ECC */
or CardAccessLevels.AccessLevelID = '7' /* Telco MDF – BellSouth Side */
or CardAccessLevels.AccessLevelID = '12' /* Media Storage Room */
or CardAccessLevels.AccessLevelID ='13' /* Receiving to Chiller Room */
or CardAccessLevels.AccessLevelID ='15' /* Service Yard to Generator Room */
or CardAccessLevels.AccessLevelID = '17' /* Door 121 ECC to RF */
or CardAccessLevels.AccessLevelID = '18' /* Door 122B War Room to RF */
or CardAccessLevels.AccessLevelID = '20' /* IT Lab Core Management */
or CardAccessLevels.AccessLevelID ='21' /* Prod RF from Vestibule */
or CardAccessLevels.AccessLevelID = '22' /* Prod RF North */
or CardAccessLevels.AccessLevelID = '23' /* IT Lab */
or CardAccessLevels.AccessLevelID = '25' /* Production Raised Floor */
or CardAccessLevels.AccessLevelID = '26' /* Battery Rooms */
or CardAccessLevels.AccessLevelID = '27' /* UPS Rooms */
or CardAccessLevels.AccessLevelID = '28' /* David Hart Build Out Crew */)
order by lastname
February 22, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

Follow the instruction below:

Open DTS designer in the Enterprise Manager.
Create a connection using Other Connection. This is your source database - MySQL database. For this you need to create an ODBC connection in advance through Control Panel>Administrative Tool. Say this is "Connection 1".
Create a connection using Microsoft OLE DB Provider for SQL Server. This is your target datbase - SQL database. Say this is "Connection 2".
Create an Execute SQL Task as below:

Enter description "Initialise". Select "Connection 2" for Existing connection. Enter the following script for SQL statement. This script will drop and create the SQL target tables.
-- Drop and create 2 SQL target tables that you need. This is my sample table. you should create your own tables.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MySQLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MySQLTable]
-- This is my sample table. you should create your own table
CREATE TABLE [dbo].[MySQLTable] (
[personid] [int] NOT NULL ,
[ingproduct] [varchar] (255) NOT NULL ,
[companyid] [int] NULL ,
[townname] [varchar] (50) NULL ,
[Customer] [varchar] (101) NULL ,
[address1] [varchar] (50) NULL ,
[address2] [varchar] (50) NULL ,
[city] [varchar] (20) NOT NULL ,
[state] [varchar] (4) NOT NULL ,
[Postcode] [varchar] (4) NULL
) ON [PRIMARY]
GO

4. Create a Transform Data Task. The command is under task.

In Transform Data Task Property, select source tab.
Select SQL query option.
Click Build Query button.
Define the tables first in the txet box using Select *. Then you can tick all the required columns later.
After complete click OK. You may add DISTINCT clause at this time as well. This means you can edit the text box.
Select Destination tab.
Select the corresponding target table. At this stage, either you already have created the empty target table or click Create button to create a new table.
Select Transformations tab.
You will see all the column mapping are correctly matched. If not, you need to manually match the columns.
Click OK.

You need do #4 twice for each table you need in SQL server.

Now you are ready to test importing MySQL data through dts.