Copy data from one mysql server to another using SSIS

create a user in mysql

CREATE USER 'admin'@'%' IDENTIFIED BY '@dm1n';
GRANT ALL TO 'admin'@'%'
FLUSH PRIVILEGES;

Create 3 Variables MaxID ,MinID and Variable


















Now add for loop,execute sql task and DFT




In Data flow task Define the source connection and Destination Connection.Use ADO.Net Connection managers for both.

In For Loop Type the number of times you want your loop to execute





















 In First Execute SQL task editor add the destination connection and Insert this in SQL Command

 set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'


In Second execute SQL task set the properties as



 
 Right click the data flow task and go to properties ->expressions ->ADO NET Source 1 command

"SELECT  EventLogID, ApiKey, ApiToken, UserID, EventDate, EventTypeID, EventCategoryID, Message, Data, CreatedDate, UpdatedDate FROM  ka_eventlog
   WHERE     EventLogID between '"+ (DT_WSTR,15) @[User::minid]+"'  AND '"+ (DT_WSTR,15) @[User::maxid]+"'"


Connect to destination Connection

Make sure you set your connection manager's properties by right clicking them and setting as

 Retain Same connection true
and
Delay Validation true

Dynamic SQL Command Variable Data Access with ADO.NET Source

There is a way to make the SQL command dynamic. In the Control Flow Tab, select the Data Flow Task that contains the ADO.NET Source and press F4. This will bring up the properties window for the DFT.

Click the expression button, opening the Property Expressions Editor, and select SQL command for the property.

Click the expression button to open the Expression Builder.

Proceed to build the expression using the tools and/or by typing the code. In this example, the variable is user defined, but you could also use a system variable. The tricky part is making sure you have double quotes all in the right places and have the correct variable type because the expression must evaluate to a string (another blog, another day).

Be sure to Evaluate Expression to see if it indeed looks like an executable SQL command.

Note: If you define a variable you might need to place a proper default Value in the variable so the SQL command will allow validation of the metadata

in order to use OUTPUT parameters to return values, you must follow these steps while configuring the Execute SQL Task:

For OLEDB Connection Types:

1. You must select the OLEDB connection type.
2. The IsQueryStoredProcedure option will be greyed out.
3. Use the syntax EXEC ? = dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT The first ? will give the return code. You can use the syntax EXEC dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT to not capture the return code.
4. Ensure a compatible data type is selected for each Parameter in the Parameters page.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the parameter marker's ordinal position. That is the first ? maps to Parameter Name 0. The second ? maps to Parameter Name 1, etc.

For ADO.Net Connection Types:

1. You must select the ADO.Net connection type.
2. You must set IsQueryStoredProcedure to True.
3. Put only the stored procedure's name in SQLStatement.
4. Ensure the data type for each parameter in Parameter Mappings matches the data type you declared the variable as in your SSIS package.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the same name as the parameter is declared in stored procedure.

For other connection types, check out the table on this page



Note: if you choose the ADO/ADO.Net connection type, parameters will not have datatypes like LONG, ULONG, etc. The datatypes will change to Int32, etc. Make sure that the datatype is EXACTLY the same type as the Variable in your package is defined. If you choose a different datatype (bigger/smaller/different type) you will get the error:

Error: 0xC001F009 at Customers: The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Add New Customer, Execute SQL Task: Executing the query "dbo.AddCustomer" failed with the following error: "The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

To fix this error make sure the datatype you select for each parameter in the Parameters page exactly matches the datatype for the variable.
For example i was fetching bigint from the query and setting it to variable with int 64 but type didint match so i had to use cast (mybigintcolumn as signed integer) as mycolumn to match the type .
If you have attempted to use a connection type other than ADO.Net with named parameters you will recieve this error:

Error: 0xC002F210 at Add New customer, Execute SQL Task: Executing the query "exec dbo.AddCustomer" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Named parameters can only be used with the ADO.net connection type. Use ordinal position numbering in order to use OUTPUT parameters with the OLEDB connection type. Eg: 0, 1, 2, 3, etc.

OUTPUT parameters are extremely useful for returning small fragments of data from SQL Server, instead of having a recordset returned. You might use OUTPUT parameters when you want to load a value into a SSIS Package variable so that the value can be reused in many places. The data that is output might be used for configuring / controlling other Control Flow items, instead of being part of a data flow task.

Travel

http://news.distractify.com/culture/32-surreal-places-that-actually-exist-on-earth-i-cant-believe-this-isnt-photoshopped/

http://www.tripadvisor.in/TripNews-a_ctr.uniquebeachesEN4

Writing to a MySQL database from SQL server using SSIS

I was unable to use the ADO.NET destination to insert data into my mysql database.

For the ADO.NET Destination to work properly, the MySQL database needs to have the ANSI_QUOTES SQL_MODE option enabled. This option can be enabled globally, or for a particular session. To enable it for a single session:

1. Create an ADO.NET Connection Manager which uses the ODBC driver
2. Set the connection manager’s RetainSameConnection property to True
3. Add an Execute SQL Task before your data flow to set the SQL_MODE – Ex. set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'
4. Make sure that your Execute SQL Task and your ADO.NET Destination are using the same connection manager.

Setting the RetainSameConnection property to True will ensure that your Execute SQL Task and ADO.NET Destination are in the same session.