September 23, 2015 at 6:44 am
We are migrating our vendor's app and would like to move all the data from the custom tables over to the new database when we move to QA and then again when we move to Prod. I was going to use SSIS, starting with a SQL Task to create a record set of all the custom tables in the database. I created two variables (TableName and TableNames). Then I created a Foreach Loop Container to loop through the TableNames. Inside that I created Data Flow Task, and inside the Data Flow Task I created an ADO Net Source, but when I put a quote in for the table.. I get an error from SSIS.
A second issue that I expect to have is that some of the custom tables have identity columns that auto increase by 1. Just doing an SQL insert into select * will not work.
September 23, 2015 at 7:40 am
Hi
Could you please tell me what the error is returned in the first issue?
Regarding the second issue, you can play with SET IDENTITY_INSERT [ database_name . [schema_name ] . ] table { ON | OFF }.
Br.
Mike
September 23, 2015 at 11:42 am
michal.lisinski (9/23/2015)
HiCould you please tell me what the error is returned in the first issue?
Regarding the second issue, you can play with SET IDENTITY_INSERT [ database_name . [schema_name ] . ] table { ON | OFF }.
Br.
Mike
The error is...
Error1Validation error. Data Flow Task: Data Flow Task: An error occurred executing the provided SQL command: "select * from ?". Incorrect syntax near '?'. Package.dtsx00
Now for the second issue.. The point of doing this in SSIS.. is for it to get a list of all the tables and then loop through them. For those with identity... I would have to first find those.. I guess I could try and maintain a table with a list of them, and what the insert into and select should look like.. hmmm
September 24, 2015 at 3:27 am
Hi again
It looks you pass parameter to ADO NET Source in incorrect way?
Please take a look for an example:http://jamessummerlin.com/2015/05/04/ssis-using-expressions-to-build-sql-statements-for-ado-net-connections/, but I prefer the Transfer SQL Server Objects Task for such events, especially if the list of objects is constant (by Script Task for dynamic list). It also resolves identity problem.
Br.
Mike
October 7, 2015 at 8:41 am
Thanks for your reply.. I am back from two weeks of vacation.. and getting back to this project.. due Monday. Looking at the transfer sql server objects task.. setting up a test, never used this object before. I am sure I could do this better.. using a table to provide a list of tables we want to move ... tables where the users are entering data, not the tables where we are keeping information that is coming from other sources and these are different in the new database from the old. But given the deadline I am just going to manually go through the tables and select them in the "Select Tables" window.
October 7, 2015 at 10:37 am
Using the Transfer SQL Server Objects Task, I am having the same issue with our identity tables.. error:
Error: 0xC002F325 at Refresh MPM's Zemeter net, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "SET IDENTITY_INSERT [dbo].[ztb_SQD_OTIF_Staging_Ma..." failed with the following error: "Table 'dbo.ztb_SQD_OTIF_Staging_Master' does not have the identity property. Cannot perform SET operation.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".
October 7, 2015 at 4:39 pm
This error is telling you that you've configured the object transfer to contend with Identity values, but your target table does not actually have an IDENTITY column in it. You have the transfer set to enable identity insert, this needs shut off.
Are you using SSIS with a transfer objects task? You could likely do this just as easy with the import/export wizard and save your package.
October 8, 2015 at 2:19 pm
John Rowan (10/7/2015)
This error is telling you that you've configured the object transfer to contend with Identity values, but your target table does not actually have an IDENTITY column in it. You have the transfer set to enable identity insert, this needs shut off.Are you using SSIS with a transfer objects task? You could likely do this just as easy with the import/export wizard and save your package.
That is interesting.. the production database was not to have any changes made to it.. during this change over, bloody heck. Thanks, got it working. We need to run this several times.. during QA process.. and on only a select group of tables (quite a number of them though).. so was looking for a good way to do this... script or SSIS, and I picked SSIS.
October 8, 2015 at 2:51 pm
The import/export wizard uses SSIS and allows you to save the package off. You could use that to do your first run, then save it off and use it for subsequent runs. This will allow you to parameterize some of the connection managers so you can point it to your different environments.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply