SSIS Package Import Problem
-
We are in the process of upgrading our servers from 2008 R2 to 2014. It has been decided though that we'll upgrade to 2016 for SSRS and SSIS, As part of this I'm importing our data warehouse load into the UAT platform.
The load consists of a series of packages called from a master package. I'm using the Project Conversion Wizard to import them from the File System to SSISDB and it's here I'm struggling. I can import all the child packages with no problems but when I try to import the master package I get the error shown.
The file name is not valid. The file name is a device or contains invalid characters.
The file name is in the same format as the other files in folder, in that it comprises only text and underscores and it's definitely not a device. The only difference is that the master package contains Execute Package tasks and the child packages don't.
Bingling around suggests that the problem is actually related to permissions so I've given the SSIS Service Account full control on the folder with no joy and I've also tried to import the package as me with no luck either. It's been suggested that giving permission to DtExec will fix it but I'm not sure how to do this.
Can anybody help?
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
You might be right about that it references other packages. In your current 2008 packages, I assume that the location of the package is the full file path? In SSISDB you won't have those same FilePaths, as within SSISDB they don't exist.
Is opening your Integrated Services project from SSDT not an option, and then deploying it from there not an option? I realise that if this is a large project it's going to take time, but you could amend the tasks to reference the other packages within the project, rather than FilePath, at that point and then deployment "should" be relatively simple.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
You might be right about that it references other packages. In your current 2008 packages, I assume that there the location of the package is the full file path? In SSISDB you won't have those same FilePaths, as within SSISDB they don't exist.
As it happens the file paths are the same between the Dev box and the UAT box but I take your point and it's something to bear in mind.
Is opening your Integrated Services project from SSDT not an option, and then deploying it from there not an option? I realise that if this is a large project it's going to take time, but you could amend the tasks to reference the other packages within the project, rather than FilePath, at that point and then deployment "should" be relatively simple.
Not at the moment; the best I could do is use BIDS on the the UAT jumpbox with an untouched 2008 version of the package. That would essentially mean having to start from scratch though and I reeeaally don't want to do that. When it comes to going live its a possibility. I might be able to do that on the live servers before we cut things over. This is not ideal.
I'm trying to write the release plan for go-live and I wouldn't be happy writing anything definitive until I've successfully migrated the packages between the two versions.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
If you are doing this:
I'm using the Project Conversion Wizard to import them from the File System to SSISDB
This statement cannot be true.
As it happens the file paths are the same between the Dev box and the UAT box .................
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Tuesday, August 22, 2017 7:19 AM
The file paths appear to be absolutely identical. At the moment the files on both servers are in a folder on the desktop and the path to both folders is the same. Admittedly this is more good luck than good judgment. Can you explain what you mean please?
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
- Neil Burton - Tuesday, August 22, 2017 7:31 AM
If a package is stored in SSISDB, it is actually stored in the database and not as a standalone file. If that package needs to call other packages, it needs to call them within SSISDB, not from the file system.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
As Phil says, the packages are actually stored as a varbinary in the SSISDB database, rather than files.
When running anoter package, you'll need to ensure it's in the same project. In the properties of the windows of the Execute Package Task Editor, you'll need to select Project Reference for Reference type in the package pane. Like below:
This, I believe is where the conversion is probably falling over. It doesn't recognise the External Reference as valid path, as it's within SSISDB, and so "falls over". This, does make me (unfortunately) come to the conclusion that you may well have to open your current project in SSDT and manually by changing the references yourself. 🙁 Then deploy the project to SSISDB.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Phil Parkin - Tuesday, August 22, 2017 8:01 AM
I see. So even though the absolute filepaths are the same, that's not the where the master package actually calls the files from, is that right? That makes sense if the packages need to be imported into SSISDB to run.
This, does make me (unfortunately) come to the conclusion that you may well have to open your current project in SSDT and manually by changing the references yourself.
Then deploy the project to SSISDB. Getting SSDT installed was actually less trouble than I feared and because the files are in relatively the same place they deployed easily enough. I've got to spend a bit of time knocking the kinks out because of a couple of differences in the UAT platform (and a missed variable name :Whistling: ) but I'm a lot closer than I was this morning.
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Then deploy the project to SSISDB. Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply