Problem with getting SSIS package to successfully execute. Looking for stop-gap solution allowing user to update a table column w/ flat file data. Advice needed.
-
I inherited support of an application that uses SSIS packages to run nightly jobs to update the database. A user recently requested adding a datetime column representing "Registration Date". The application's database tables do not currently have this value. It is obtained from a flat file read by the SSIS package. So my plan was to modify the SSIS package by enabling an additional column for "RegDate" so that during the SSIS job, the registration date column data from the flat file could be used to update a column in the desired database table, in which I would add a column "RegDate".
Unfortunately, when I make the changes to the SSIS package and try to execute, I get this error:
[SSIS.Pipeline] Error: The component metadata for "SP Training Certificate Types, clsid {33D831DE-5DCF-48F0-B431-4D327B9E785D}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Googling the error, it seems that the problem is a version mismatch between SQL Server/SSIS version I'm using locally in my MS Visual Studio development environment and what's being used by the server. Looking at the SQL Server instance (dev server), I see the following: SQL Server 12.0.5203.0. So, I tried updating my local install to the same version to no avail. At this point, I'm not sure what is left that is mismatched. It may require more time for me to pin down, and in the meantime I would like to enable the user somehow and thought one stop-gap measure could be to allow the user from the application form to manually press a button that would update a specified database table column with data from a specific column matching a specified name from the flat file and copy that flat file data to update the database column desired.
Would this be pretty straight forward? Is there any vulnerability or issue I should be aware of about using this as a stop-gap measure?
It'd really be best if I could get some tip on how to resolve the SSIS error above, as automating the process would be the end goal. But short of that, some general direction on how to update a database table column using a flat file (allowing the user to do this somehow from the application) would be appreciated.
Thanks in advance.
-
What version of SSDT and VS are you using? If you're using VS2015/2016 have you set your Target Server Version correctly? You can check it by right clicking the project in the Solution Explorer and selecting Properties. Go to Configuration properties, General, and the TargetServerVersion option will be displayed. Have you changed this to 2014, as VS2015 will default for 2016.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
The application in Visual Studio has 6 projects. So I went to the SSIS project and right clicked on Properties for that, then clicked Configuration Properties, then General. Deployment Target Version shows as SQL Server 2014.
When I right click on properties for the Client Winform project, there are a bunch of options (Application, Build, Build Events, Debug, Resources, etc), but no Configuration Properties.
When I right click on properties for Solution up top that shows 6 projects total, and I click on Configuration Properties, the only option below that is Configuration. General does not exist. So I click Configuration and it shows Configuration: Debug, Platform: Active(Any CPU). It shows this for all 6 projects: Debug ; Any CPU, except the SSIS project(SQL Server 2014) shows Development for configuration and blank for Platform. Build checkbox is checked for all 6 projects.
Version of Visual Studio I am using is Enterprise 2015 w/ update 3 & .NET Framework 4.6.01055. SQL Server Integration Services shows as installed but no version number reports. Update: SSDT version using locally is 14.0.61021.0. How do I find out what's being used on the server?
The below is the output when attempting to execute the SSIS job:
Hope the above helps.
-
Anyone? Would really appreciate it if someone could provide some leads on how to resolve this. Thanks in advance!
- SQLNewbie777 - Tuesday, June 27, 2017 4:10 PM
What is the 'SP Training Certificate Types' component? What does it do?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I'm not positive what it does because I'm taking over support of the application due to the person who had been supporting the app leaving. I'm guessing it just breaks down the types of training certificates available. The app tracks required employee training courses.
Am hoping to get some leads on how to figure out what exactly is causing the errors in my earlier post with screenshot and how to resolve them.
- SQLNewbie777 - Wednesday, June 28, 2017 8:44 AM
As the error message you provided refers specifically to that component, I'm not sure that anyone here can offer more help without knowing what it is and what it does.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I'm wondering if the clsid or guid expected, as it existed on the previous developer's local system, doesn't exist on my local machine where I'm trying to rebuild the app, and that is causing the problem?
-
Looking at the error and the naming of things in there, I'm guessing you are missing sharepoint connectors on your system.
The reason I think this:
1 - you are getting a "missing connection manager" error
2 - it works on a different system (thus you are likely missing something)
3 - the screenshot indicates it is pulling data from sharepointI'm not positive, and can't find much on those class ID's, but if you have access to the system which is successful at building the package, might not hurt to scour the registry on that machine to map up the class ID (clsid) with a registry folder and find out exactly what piece of software it is.
If I am correct about the sharepoint connetors, you can get them (and more information) here:
https://msdn.microsoft.com/en-us/library/hh368261.aspxBut I think if you fix the connection manager issue, the other errors will go away.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Thanks, let me give your suggestions a try. I'm hoping I don't have to try to gain access to the working dev system because the person left and I wouldn't have access to the login account. Plus, it sounds like a bit of a headache to remap.
Upon initial research, I thought it could be due to some SQL Server version discrepancy between what was used on the system that created the last build of the app and my own system's versions used. Do you think that could still be the case, be it SQL Server or some other related program?
- SQLNewbie777 - Wednesday, June 28, 2017 1:47 PM
Can you build the solution on your own machine, or do you get issues with that SP component?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I can build the solution on my own machine with 3 success, 0 failed, 0 skipped. The app loads fine when I run it. I'm doing this on debug build and running the built executable. The project is broken up into 6 sections, the application client, the web version, SSIS, domain, and etc. I can also successfully rebuild the SSIS part only with 0 errors.
It is only when I open the dev version of the SSIS dtsx package to right click on the desired package to execute, do I see the error messages in Execution Results output (the screenshot I provided).
When I look at the Control and Data Flow charts, I am able to resolve most errors by entering them and then exiting them. The only error I see remaining is with: Import SharePoint Training Certs, the chart still has an X next to it. When I hover over that chart with mouse, I see the error about "Component Metadata for SP Training Cert.. could not be upgraded to newer version." When I click Edit to enter that and see more in depth, I see now errors or X in any chart.
-
It could be SQL server version related, but if you are running it from your local machine, it shouldn't much matter from my experience. I had an SSIS 2016 package I built up in Visual Studio 2015 connecting to 2 SQL Server 2008 R2 instances (one as a source, one as a destination) and it was all good and happy until I went to deply it to our SSIS 2012 server. Well, it deployed successfully, then barfed all over the place about how 8 cannot be larger than 6 for the version number (or some odd numbers like that that didn't seem to reflect the SQL server version at all).
Some tiny bit of annoyance I found as well with SSIS that may bite you later on: Make 110% sure your SSMS version matches your SSIS version if you are using Integration Services Catalog mode.
If you use SSMS 2016 to export an SSIS package from a 2012 SSIS server, it will export your ispac all happily, and it will re-import successfully too, but it will fail to run as when you exported it from SSMS 2016, it very unhelpfully upgraded it for you too.
Don't know if this is fixed in an SP or in 2017, but I've just been getting into the habit of connecting from SSMS to the same version of SQL Server (ie 2008 to 2008, 2005 to 2005, 2012 to 2012, 2016 to 2016... that is all I really manage).Also, you wouldn't need to re-map the class ID to anything as that'd likely create more headache. What you'd be doing is using the working system to figure out what software you are missing to get that class ID to show up in your registry. I've had to do that with COM objects in some weird pieces of software before. End users say they get an error and attach it in an email. I scour my registry to figure out what software it is and see if it needs a DLL registered or a whole software package installed, and then pass that off to the IT guys to tackle (usually just an email like "can I get you to install office on PCXXXX?").
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Before I look into this further through the leads you've provided, all I'm trying to ultimately do is the following:
1. We have an application already published and in use in production environment without issue.
2. Recently received a customer request to allow generated Reports (thru Stored Procedures) to have a new "Registration Date" column appended to the reports.
3. Looking through the application's database tables, there is no existing "Registration Date" column in any table. This is when I learned that the "Registration Date" data is obtained from a flat file that is processed by SSIS job. The flat file comes from InfoPath SharePoint form inputs and is a simple excel file. "RegDate" is present in this flat file as a column under the header.4. Originally our reports had a column for "RegDate" but it was removed for some reason by the maintainer (who left the team). The customer simply wants it back. So, in order to do that, I looked at the existing (dev version) of the SSIS package and see that there is an import performed on the flat file and I can enable the "RegDate" column as part of the SSIS job. I could then create a "RegDate" column in an existing table used by the application to get the "RegDate" data from the Reports Stored Procedures.
5. ANOTHER option: I can add a button to the Reports screen of the application, and have that button perform an import from the flat file data source to the desired database table (where I would create a new column "RegDate") to hold the values. I think this would unblock me and enable me to provide a stop-gap measure for the customer to be able to have Reports generated with a Registration Date column and data, while I attempt to figure out the SSIS package error messages (mentioned in my earlier posts) and resolve them. Does this sound like a feasible stop-gap solution?
- SQLNewbie777 - Wednesday, June 28, 2017 2:28 PM
It sounds like ti should work. My only concern with that would be that once it is "working", you could end up with data drift between the sharepoint version of the document and the flat file data source version of the document. Or you could have Joe update the sharepoint version of the document while Bob updates the flat file version. Which one becomes the master?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply