Possible? If so what do I use to build it? Help!
-
Let me begin by saying I have no idea what I'm doing.
The need:
Create one SSIS to import 210 tables from 30 different SQL DBs to 1 SQL DB in 2 weeks. (I know, sounds like your company right?)
The SSIS should begin by reading the servername of the (30 dbs) from the final db.
- All of the dbs have the same table names and schema. Only difference is ServerName. All login creds will be the same as well.
The SSIS should then pass a single servername as a variable to the import tasks
- Should these be separate dtsx?
It should complete one set of 200 imports and then move to the next servername pass or fail.
Any ideas?
I don’t have much experience with this but my original thoughts are:
Read the list of servernames from the database.
ForEach task to read the servername into a variable
Create a connection based on that variable
Execute the 200 imports using that connection.
Is that possible and if so how, seriously I don’t know where to begin.
Thanks
-
I find this very strange. I wuld think the requirements are to import 210 tables from 30 SQL Servers. Why does it have to be specifically with an SSIS package? There are other ways of doing this, if you're not familiar with SSIS.
Otherwise: buy a book on How To with SSIS, there are several around. Also, tell the assignmentgiver to postpone the targetdate, or hire a professional to do the job.
Greetz,
Hans Brouwer -
Wow Hans, thanks for that response.
You managed to not only not answer any of my questions, you also managed to not share any of your "professional" experience and even failed to suggest any particular book.
Good stuff.
Thanks again.
-
I'll try to be a bit more helpful than Hans ...
I've been trying to do a similar exercise with SSIS, multiple databases on the same server into an amalgamated database on another server.
The one brick wall I've run into is finding out how to change the dataflow properties to refer to the different databases. Seems to me that while SSIS is faster and more feature packed than DTS, it has lost some of its basic configurability
. If someone has discovered how to do it, I'd love to know 
You might be better served using some generic scripting using BCP and/or BULK INSERT. That way you can parameterise a lot of the work.
Are the servers in remote locations? If so, you might also need to look at some sort of extract/ftp/import process rather than a straight import.
--------------------
Colt 45 - the original point and click interface
-
You are on the right track.
Store the name of the package, servers, databases and tables in tables and then develop a stored procedure that loops through them appropriately, calling the OLE stored procedures. I have included some code from one of our procs that does this. I hope it helps.
The name of the package is also in a table. The proc populates DTS package global variables. This was done using SQL2K.
In the package a Dynamic Properties task (you can use an Activex task also) uses those global variables to modify the pertinent tasks with the source and destination servers/databases. SQLDTS.com has examples on how to do this.
I used one task per table because I did not have that many tables. With many tables I would bite the bullet and create one task per table or if I had time, I would expand this approach by creating the package to process one table, then convert that package to Visual Basic Script (in DTS editor, Save as, and in Location specify Visual Basic File) and use the pertinent sections of the result to GENERATE the data pump task of the other tables.
I originally received help in this regard from Darren Green’s seminal article on the subject:
http://www.databasejournal.com/features/mssql/article.php/1459181
What follow is a code snippet from our stored procedure that does this.
------------------------------------
--Obtain destination server metadata
------------------------------------
SELECT @DestinationServer = ServerName,
@DestinationServeruserID = UserID,
@DestinationServerPassword = Password,
@DestinationDB = DBName
FROM TransmissionServers
WHERE TransmissionID = @TransmissionID
AND IOFlag = 'O'
IF @@ROWCOUNT = 0
BEGIN
SELECT @Message = 'No destination server specified for transmission ' + cast(@TransmissionID as varchar(6)) + ' in Transmission Servers table. Process terminated.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
drop table #SaveServer
RETURN -9
END
ELSE
BEGIN
SELECT @Message = 'Destination Server for transmission is ' + @DestinationServer
EXec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
END
------------------------------------------------------
-- Declare source servers cursor, open and first fetch
------------------------------------------------------
DECLARE SourceServers CURSOR FOR
SELECT ServerName, userID, Password, DBName, OverrideSQL
FROM TransmissionServers
WHERE TransmissionID = @TransmissionID
AND IOFlag = 'I'
OPEN SourceServers
FETCH NEXT FROM SourceServers INTO @SourceServer, @SourceServeruserID, @SourceServerPassword, @SourceDB, @OverrideSQL
-----------------------------------------------------------------------------------
-- Transmission Loop
-----------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
-------------------------------
-- Create/Load a Package Object
-------------------------------
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
SELECT @Message = 'Create Package object failed. Problem with extended procedure sp_OACreate. Process terminated.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG, @hr
SELECT @abend = 1
BREAK
END
if @PackageServerUserID is null or @PackageServerUserID = ''
SET @Cmd = 'LoadFromSQLServer("' + @PackageServer + '", NULL, NULL, 256, , , , "' + @Package + '")'
else
SET @Cmd = 'LoadFromSQLServer("' + @PackageServer +'", "' + @PackageServerUserID + '", "' + @PackageServerPassword + '", 0, "' + @PackagePassword + '", , , "' + @Package + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
SELECT @Message = 'LoadFromSQLServer failed for package ' + @Package + '. Return code is ' + rtrim(cast(@hr as varchar(6))) + '. Process terminated. @Cmd=' + @Cmd
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
SELECT @abend = 1
BREAK
END
SELECT @Message = 'Package ' + @Package + ' has been created and loaded from ' + @PackageServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
------------------------------------
-- Populate package global variables
------------------------------------
SELECT @TransmissionResult = 1
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("TransmissionID").Value', @TransmissionID
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("Package").Value', @Package
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("SourceServer").Value', @SourceServer
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("TransmissionResult").Value', @TransmissionResult
SELECT @Message = 'Package ' + @Package + ' about to be executed for Source Server ' + @SourceServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
-------------------------------------------------------------------
-- Execute package, retrieve global variable that shows pkg status
-------------------------------------------------------------------
EXEC @PackageRC = sp_OAMethod @oPKG, 'Execute'
EXEC @hr = sp_OAGetProperty @oPKG, 'GlobalVariables("TransmissionResult").Value', @TransmissionResult OUTPUT
---------------------------------------------------------------------------
-- If @TransmissionResult is still 1, there was an error in the package
---------------------------------------------------------------------------
IF @TransmissionResult = 0
BEGIN
SELECT @Message = 'Package ' + @Package + ' executed OK for ' + @SourceServer
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
INSERT #SaveServer VALUES(@SourceServer,
NULL,
NULL,
NULL,
NULL,
'OK')
END
ELSE
BEGIN --Log failure, fail the problem transmission and continue
SELECT @Message = '*** ' + @Package + ' failed for ' + @SourceServer + ', RC=' + cast(@PackageRC as varchar(8)) + '. Ignored, process continued. ***'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
INSERT #SaveServer VALUES(@SourceServer,
NULL,
NULL,
NULL,
NULL,
@Message)
END
-------------------
-- Clean up package
-------------------
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
SELECT @Message = 'UnInitialize failed; sp_OAMethod problem.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
RETURN -9
END
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
SELECT @Message = 'Package destroy failed; sp_OADestroy problem.'
Exec @rc = dbo.usp_LogMessage 'Stored Procedure', @Application, @TransmissionID, NULL, @Message
Exec dbo.usp_displayoaerrorinfo @oPKG , @hr
SELECT @abend = 1
BREAK
END
--------------------------
-- Get Next Source Server
--------------------------
FETCH NEXT FROM SourceServers INTO @SourceServer, @SourceServeruserID, @SourceServerPassword, @SourceDB, @OverrideSQL
END
-----------------------------------------------------------------------------------
-- Finalization
-----------------------------------------------------------------------------------
close SourceServers
deallocate SourceServers
-
Thanks Phil.
This is what I have so far...
I have a table with all server/dbproperties I'll need for connections and scheduling. The 30 DBs are International DBs. Maybe I'll add encrption later.
Created a task to get the connection info then into a for each loop that feeds the string as a param into a connection.
Thats all I have for now. But maybe you could use that idea..
-
excellent Carlos
-
Yep, you can do that.
Here is a article on CodeProject that will show you how to cycle through connections to different servers. I built on it to create a Login audit system.
Using the Foreach ADO enumerator in SSIS
-
I think my problem stems from the fact that the databases all reside on the same server. So in my case the database name is what changes.
--------------------
Colt 45 - the original point and click interface -
Then just change the Initial Catalog parameter in the connection string.
Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True;Network Library=dbmssocn;
-
Wow! Sarcasm!
D_George, I am sorry te read you don't like my answer. I merley wished to point something out, which I hoped I did, but I doubt that...
On not giving you any solutions: it's very nice to get 1, but I had the impression you're completely new in SSIS. If so, you would be better off to start with the basics, buy a book on SSIS. There are many books about the subject, which 1 would I recommend? I can't, since I have not used a book on SSIS yet. However, if you Google the internet, or search just within Amazon.com with apropriate keywords, you'll find plenty titles.
I hope solutions given here are of help, good luck with your task, I really mean that.
Greetz,
Hans Brouwer -
Hans, no harm no foul.
I'll have another post later about passing variables from an "execute package task" to the dtsx it is attempting to execute. If that is possible and how?
Maybe you could hit that one.
Thanks
D
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply