Почти типовое решение.

Постановка задачи такая - выгрузить данные в XML и отправить по FTP. Плюс к этому, пароль от FTP может регулярно меняться.

В итоге пакет выглядит так:

В пакете объявлены следующие подключения:

Чтоб FTP Task мог использовать файл из connection manager-а "users.xml" (так называлось в моём случае) тип использования должен быть не "Create File", а "File Exists". Но файла то нет вначале. Кроме того, пароль к FTP хочется менять как-то снаружи пакета и не налетать на защиту. Для решения этой проблемы служит задача "Sef FTP password and change file usage type":

public void Main()
{
  ConnectionManager FTPConnectionManager, FileConnectionManager;
  
  //Set variable to an existing connection manager
  FTPConnectionManager = Dts.Connections["FTPServer"];
  
  //Set connection manager property "ServerPassword"
  FTPConnectionManager.Properties["ServerPassword"].SetValue(FTPConnectionManager, Dts.Variables["FTPPassword"].Value);
  FileConnectionManager = Dts.Connections["users.xml"];
  FileConnectionManager.Properties["FileUsageType"].SetValue(FileConnectionManager, DTSFileConnectionUsageType.FileExists);
  Dts.TaskResult = (int)ScriptResults.Success;
}

Для задачи "FTP Task" нужно установить свойство DelayValidation = true. Разумеется переменную "FTPPassword" выносим в конфигурацию.

В Data Flow задаче для записи используется Script Component и там есть есть другой момент — как записать XML, чтоб при этом атрибут Encoding был указан? Решения найти можно, приведу своё:

XmlTextWriter xw;

public override void PreExecute()
{
  base.PreExecute();
  StreamWriter output = new StreamWriter(
    new FileStream(Connections.Conn2xmlfile.ConnectionString, FileMode.Create),
    System.Text.Encoding.UTF8); // definition by StreamWriter made for encoding="UTF-8" attribute
  xw = new XmlTextWriter(output);
  xw.WriteStartDocument();
  xw.WriteStartElement("rootElement");
}

public override void PostExecute()
{
  base.PostExecute();
  xw.WriteEndElement();
  xw.WriteEndDocument();
  xw.Close();
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
  xw.WriteStartElement("recordElement"); // open block
  xw.WriteElementString("fieldElement1", Row.Col1);
  xw.WriteElementString("fieldElement2", Row.Col2);
  ...
  xw.WriteEndElement(); // close block
}

На выходе получается XML вида:

<?xml version="1.0" encoding="utf-8"?><rootElement><recordElement><fieldElement1>..</fieldElement1><fieldElement2>..</fieldElement2>..</recordElement><recordElement>..</recordElement>..</rootElement>