March 19, 2012 at 12:02 pm
I have three package that load 69 tables from an AS400.
I need to load another 69 tables with exactly the same table names but a different schema.
Is there a way to find and replace in SSIS?
Can't you view the XML and change that?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 12:06 pm
You could probably edit the XML directly but don't do a replace all, I'd do a replace and look at each one just to be sure but it would still be less painful to do it that way than search and type and search and type.
CEWII
March 19, 2012 at 12:15 pm
Dumb question but what what is the best way to edit the XML?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 19, 2012 at 1:44 pm
Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.
Steve.
March 20, 2012 at 5:18 am
Could you use a broker package that would reuse the existing packages that are there?
Each package could load in the details of the source and destination depending on a number passed down to it (which could be specified by the broker).
So to give an example...
Broker package has a for loop which would call a SQL query that would return 2 rows from a table, these two rows would contain an ID for each use of the package (so 1 and 2).
This variable would be passed down to the package that does the work.
In the worker package it would execute a SQL task which would get the SQL for the source and destination and load the queries into a variable which the data flow task would use.
Hope that helps?
Another option would be to create a template package (http://support.microsoft.com/kb/908018) although I've not tried that myself.
Graham
March 20, 2012 at 6:07 am
Another option would be to create a template package (http://support.microsoft.com/kb/908018) although I've not tried that myself.
I use template packages quite a bit (one for each of my data warehouses) and I find they work quite well 😀
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
March 20, 2012 at 6:51 am
stevefromOZ (3/19/2012)
Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.
The File open did not work for me. I can edit in Notepad.
If you drag the file into SSMS how do you save the file?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 20, 2012 at 5:30 pm
You can of course just do your work in notepad and save it as per any file.
SSMS shouldn't be any different.
P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.
March 21, 2012 at 5:32 am
sam.dahl (3/20/2012)
You can of course just do your work in notepad and save it as per any file.SSMS shouldn't be any different.
P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.
All that I need to change is the schema and the descriptions for each task, etc to reflect another schema.
Notepad it is.:-)
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 21, 2012 at 8:13 am
sam.dahl (3/20/2012)
You can of course just do your work in notepad and save it as per any file.SSMS shouldn't be any different.
P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.
I would generally agree, however I usually go a step further and also make sure that I review, major and minor version and reset build version to 0 (zero). I will also sometimes change the creation properties stored to reflect what I am doing.
CEWII
March 26, 2012 at 10:40 am
Welsh Corgi (3/20/2012)
stevefromOZ (3/19/2012)
Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.The File open did not work for me. I can edit in Notepad.
If you drag the file into SSMS how do you save the file?
I run this query (have it saved as a snippet) and then click on the XML in the resultset to open the XML editor.
SELECT CONVERT(XML, '<root/>');
From here you can copy/paste new XML into the window.
File>Open (Ctrl+O) to open new XML, even when starting in a SQL Query Window should work fine though. If not, check the encoding of your saved XML file. SSMS tends to play games with files saved as Unicode with no signature at the beginning of the file (e.g. 0xFF 0xFE).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 11:01 am
Thats pretty slick. I revised the 3 SSIS Packages on Friday.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply