SSIS - Query to XML File, FTP XML File to Server, Delete Local XML File and Mark Records as Sent.

• unleashedmaniac

  Mr or Mrs. 500

  Points: 512

  More actions

  December 23, 2010 at 10:20 am

  #232289

  Hello,

  I have a task to FTP records via a XML file to an outside vendor.

  I already have two stored procedures written that are needed for the task.

  One stored procedure returns 3 columns (FileName, XMLData & isSync). The FileName column contains the XML FileName which is dynamic, XMLData contains either a small set of transaction records or the entire table and the isSync column returns a flag to indicate if the procedure is returning transaction files or the entire table. This procedure grabs newly updated records in a table and sends them in a transaction file and once a day sends the whole table to re-sync our table with their table. This procedure always only returns one row. The SSIS package will run every 15 minutes and might or might not return any rows which would or wouldn't require an XML file.

  The other stored procedure marks all of the sent files as "sent".

  I already created an SSIS package that seems to work, but I don't know if my process is overkill or if there is a better way to go about accomplishing my task.

  Here is my current project:

  1. Created 4 project level variables adoVar(Object), FileName(String), XMLOut(String) and isSync(String).

  2. Dataflow task

  a. OLE DB Source - Stored Procedure that returns FileName, XMLData & isSync flag.

  b. Recordset Destination - Destination set to adoVar variable and included the columns FileName, XMLOut and isSync.

  3. Foreach loop container - ADO Enumerator source variable set to User:adoVar(Object). Mapped the FileName, XMLData & isSync variables to 0,1 & 2.

  a. Dataflow task

  1. Script Component - ReadOnlyVariables set to User::FileName & User::XMLOut. Added script code to set variable values.

  2. Export Column Component - Extract Column = FileName; File Path Column = XMLOut, Force Truncate (writes XML file).

  b. FTP Task - Upload file. LocalVariable = User::FileName

  c. File System Task - Delete file. Source set to a Dummy.XML datasource. Set the Dummy.XML expression "ConnectionString" = User::FileName

  d. Execute SQL Task - Direct Input - Parameter Mapping @varIsSync = User::isSync -

  SQLStatement = declare @varIsSync

  set @varIsSync = ?

  if @varIsSync = 'N'

  begin

  exec sp_MarkRowsSent --Marks rows in table as sent

  end

  Thanks for any advise.